Get Max value in Subtotal and to Sum up Subtotal value in Grand Total

[Judy_S]

Hi,

I would like to display the Max value in subtotal, but to get sum up value in Grand Total. 

So far, I can get the Max value in subtotal, but the Grand Total also get the Max vaule of subtotal, see below screenshot:

How can I get a sum up subtotal values for grand total, not just the Max Subtotal value?

Thanks,

[v-danhe-msft · Accepted solution]

Hi @Judy_S ,

Is it a measure or a calculated column? I suggest to refer below link about the problem for sum of total:

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

If it could not help you , could you please post a pbix file to have a test if possible?

Regards,

Daniel He

[Judy_S]

Hi Daniel,

Thanks for the link, I think that could be a work around to get what I want, but it looks like we need to created another column for subtotal and grand total value in order to display the correct number. I initially wanted the subtotal and grand total could be displayed in the same colume with cost.

The "Cumulative Cost" column value was grouping and calculated(sum by group) from data source and saved as another table. Then I used this table to build up line chart and Matrix table. I selected the Cumulative Cost to display as "Maxium" vaule in Fields pane setting so that the subtotals show the maxium value among the Cumulative Cost, so does the grand total shows maxium value among the Company. 

I was hoping I can find the measure fuction selection from "Grand Total" in Format pane, but there are only for formatting, not for any measure fuction setting. 

Thanks for your reply, I will try that work around way to solve this issue, Thanks,

Judy Shih