How to create a new subtotal column from Measures ?

[Birinder]

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]

@Greg_Deckler 

Hi 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

[Greg_Deckler · Accepted solution]

@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])