Measure sum is not correct in table chart

Hi Mayank08 ,

 

Measures are calculated based on context so when you are comparing the MAX value of a column when you go to the totals the calculation is also based on the max value of that column so in your case the measures is looking for the max value of mediaName and return the sum of the value.

 

You need to add a different context to your calculation something similar to this:

 

spend_MTM =
SUMX (
    sourcegroup,
    ROUND (
        SWITCH (
            MAX ( sourcegroup[mediaName] ),
            "HomeAdvisor", [HomeAdvisor_MTM],
            "MovingCompanies", [spendMovingCompanies_MTM],
            "Movers.com", [spendMovers.com_MTM],
            "Moving.com", [spendMoving.com_MTM],
            "QuoteRunner", [spendQuoteRunner_MTM],
            "QuoteRunner Transfer", [spendQuoteRunnerTransfer_MTM],
            "QuoteRunnerExclusive", [spendQuoteRunnerExclusive_MTM],
            "Google Local Services", [spendGoogleLocalServices_MTM],
            "Google Joe", [spendGoogle_MTM],
            "Bing Joe", [spendBing_MTM],
            0
        ),
        0
    )
)

 

Also I made the change from the nested IF to a switch function to make it more compreehnsilble.

 

Using a aggregator function (the ones that end in X) you are calculating the SUM row by row of the table.

 

The measure may need adjustmetns.

Hi Mayank08 ,

 

You may create a measure like DAX below to replace your original measure to display in Matrix visual. In your scenario, the [your original measure] is [Spend] .

 

 

Total Sum= 
VAR _table = SUMMARIZE('Fact Data',[the first displaying column of your Matrix visual],"_Value",[your original measure])
RETURN
IF(HASONEVALUE([the first displaying column of your Matrix visual]),[your original measure],SUMX(_table,[_Value]))

 

 

For reference:

https://community.powerbi.com/t5/Desktop/Maximum-value-and-total-by-group/m-p/727857#M351273 ,

https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907 .

 

Best Regards,

Amy 

 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi   Mayank08 ,

 

Does that make sense? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

 

Best regards

Amy