Incorrect Sum on Matrix Table - Need Top Summary Line to Sum Instead.

[SparkDataGuru]

Currently been running into an issue with this Matrix table below.

We have a store and it's subdepartments. 

The formula to 'Recalculated New Leads' is taken by adding the Raw Leads + Partial Unknown Leads (based on the ratio of Raw new to Used leads). All the components for the formula can be found on column 2 to 5. 

It seems each row of this table is correct when taken into isolation for the calculations. However, we're having trouble getting the the top bold line to sum correctly based on the sub-departments. How should we go about in DAX to get the Store Level row to sum rather than do the formula utlized in the sub-department lines?

Thanks for your help!

[Ashish_Mathur · Accepted solution]

Hi,

Does this measure work?

Measure1 = SUMX(VALUES(Data[Sub-departments]),[Recalculated new leads])

[SparkDataGuru]

Thanks Ashish, looks like that revised measure works! 

[Ashish_Mathur]

You are welcome.

[Greg_Deckler]

@SparkDataGuru First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

Also this: Matrix Measure Total Triple Threat Rock & Roll - Microsoft Power BI Community