MDX calculation should not work on subtotals in excel

Question

My granule record-set is like this:

I use the calculation below to avoid duplicate sum, [Measures].[TRANSACTION TOTAL AMOUNT] / [Measures].[TRANSACTION ALL COUNT] so, for TranId=1; 450 / 3 = 150

it works on every single TranId fine and I want to see: 150+80+70 = 300 excel pivot subtotal. But it shows: 910 / 19 = 47.89. I don't want it to calculate my mdx calculation for totals or subtotals! I just want dummy summarize

Answer 1

To answer your question as written, turning off the subtotal can be done with this added statement in your MDX script:

[Your Dimension Name].[TranID].[All] = null;

However I think you are modeling your data incorrectly. You just need to add a new measure group which has one row per TranID and create a Sum measure to get proper details and totals.