Forum Discussion
Helper IVHelp in calculating total
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
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]))
4 Replies
- Greg_Deckler
Community Champion
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
itsmeanujGreg_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
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]))