Grand Total of a divided calculated measure

[mintea]

Hello,

I have three measures

Current Premium = (sum(Data[Premium]))

Prior Quarter Paid = (CALCULATE(sum('Data'[Paid]),DATEADD('Data'[Valuation Date], -1, QUARTER))

Prior Quarter Premium = (CALCULATE(sum('Data'[Premium]),DATEADD('Data'[Valuation Date], -1, QUARTER)))

I created a measure such that: 

measure = ((Prior Paid/Prior Premium)* Current Premium)

The rows are displaying correctly, however I need the the grand total to be the sum of the rows and not its own calculation.  I figured I can use a sumx but I also have other filters such as year, client, and business type

The actual total for the table on the right should be 484,346

Likewise if I add more levels to the matrix you can see ABP, CP the bolded total is 4,849 while the sum of the parts is 4,548 which is correct. 

Can you please advise on how to solve this problem

[v-yalanwu-msft · Accepted solution]

Hi, @mintea ;

I create a simple example to tested it. 

Prior Quarter Paid = CALCULATE(sum([ Sales]),DATEADD('financials'[Date], -1, QUARTER))

Prior Quarter Premium = (CALCULATE(sum([Gross Sales]),DATEADD('financials'[Date], -1, QUARTER)))

Measure = [Prior Quarter Paid]/[Prior Quarter Premium]*SUM([Gross Sales])

The final output is shown below: the total not right.

So we could create another measure.

Measure 2 = 
 IF(ISINSCOPE('financials'[Year]),[Measure],
  SUMX(SUMMARIZE('financials',[Segment],[Country],[Year],"1",[Measure]),[1]))

The final output is shown below:

You can use it as a reference, or provide your simple file if necessary.
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VahidDM]

Hi @mintea 

Check this link, I think it would be helpful and you will find the solution:

https://www.vahiddm.com/post/why-my-measure-returns-the-wrong-total

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/