CODEWITHSUNDEEP
excelmdx
togy
togy

Reputation: 15

MDX calculation should not work on subtotals in excel

My granule record-set is like this:

record set

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

rows view

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

Upvotes: 1

Views: 127

Answers (1)

GregGalloway
GregGalloway

Reputation: 11625

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.

Upvotes: 1

Related Questions