Help in calculating total

[itsmeanuj]

Hi Guys,

I am looking for some help on one of the DAX calculation. Below is the sample data

Version	Month	Value
FY	Jan-23	$            92,447
FY	Feb-23	$         8,74,925
FY	Mar-23	$       18,14,881
IP	Jan-23	$         7,17,083
IP	Feb-23	$         7,17,083
IP	Mar-23	$         7,17,083

Expected Output - I want to show FY vs IP difference in the matrix format like below. Here is the DAX formula for FY vs IP

FY_vs_IP = abs(([Sum_of_FY]-[Sum_of_IP]))

	Jan	Feb	March	Total	Expected Total
FY vs IP	6,24,636.68	1,57,841.97	10,97,797.82	6,31,003.10	18,80,276.47

Now the problem is with the "Total" Calculation. i want it to calculate it as Sum(abs(Jan(FY)-Jan(IP)), abs(Feb(FY)-Feb(IP)),abs(March(FY)-March(IP))). However, the above DAX formula is giving me results as abs(sum(FY) - sum (IP))

Can someone please help in tweaking the formula to get the expected results?

Thanks,
Anuj

[Greg_Deckler]

@itsmeanuj 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

[itsmeanuj]

@Greg_Deckler - Really appreciate your response on this. It worked for me but just need to add small clause of month. I need to calculate 10 metrics using same methodology. one of the metric requires me to restrict the months to Q1 only. so can you please help me how we can add that Month filter in the expression below? Month column contains values like 1,2,3..etc.

FY_IP_DIff =
VAR __table = SUMMARIZE('2023 Data',[Time Period],"__value", abs([Sum_of_Actuals]-[Sum_of_IP]))
RETURN
IF(HASONEVALUE('2023 Data'[Time Period]),abs([Sum_of_Actuals]-[Sum_of_IP]),SUMX(__table,[__value]))

[Greg_Deckler · Accepted solution]

@itsmeanuj Try something like:

FY_IP_DIff Q1 =
VAR __table = 
  SUMMARIZE(
    FILTER('2023 Data', [Month] IN { 1, 2, 3 }),
    [Time Period],
    "__value", abs([Sum_of_Actuals]-[Sum_of_IP]))
RETURN
IF(HASONEVALUE('2023 Data'[Time Period]),abs([Sum_of_Actuals]-[Sum_of_IP]),SUMX(__table,[__value]))

[itsmeanuj]

Thank you so much @Greg_Deckler . I am new to PBI and need help on one more visual. Posted another query here - https://community.powerbi.com/t5/Desktop/Difference-between-2-bars/m-p/3135206#M1060479