Forum Discussion
Helper IIIHow to create a new subtotal column from Measures ?
Hi there,
As in the first picture you can see that I have 3 columns. What I want to achieve here is the exact solution as picture second. Now I will explain.
As you can see there is a column named as "Quarter", which contains 2 values-Q1 and Q2. I want to total the sales column values according to the Quarter Category. Same as it is happening in the 2nd Picture. I want to do this, to calculate the margin afterwords.
BUT HERE IS A TWIST:
I know how to do this, in M code and Power Query. But I want the solution, If we want to create this kind of column as a new measure. I can't figure out the solution for this case.
Thanks and regards.
Birinder I think you want:
Sub-Total Each Category Measure = VAR __Quarter = MAX('Table'[Quarter]) RETURN SUMX(FILTER(ALL('Table'),[Quarter]=__Quarter),[Sales]) Margin Measure = [Sub-Total Each Category Measure] - MAX('Table'[Sales])
2 Replies
- Greg_Deckler
Community Champion
Birinder I think you want:
Sub-Total Each Category Measure = VAR __Quarter = MAX('Table'[Quarter]) RETURN SUMX(FILTER(ALL('Table'),[Quarter]=__Quarter),[Sales]) Margin Measure = [Sub-Total Each Category Measure] - MAX('Table'[Sales]) 
BirinderHi there,
Thanks for the reply.
I want to ask you, Will this work if All the three columns ; "Quarter", "Model" and "Sales" are from 3 seperate tables, and are connected via relationship.
If Yes, then Inplace of "table" in code given by you, What am I supposed to enter.
Thanks and Regards