Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am working on a visual that will summarize results from two key tables based on filters. I am able to get the results correctly by Row however, my total is incorrect. I have tried placing each Var around a Hasonefilter and works well individually but not when I try to combine. How can I get my total to sum correctly as showin the table below?
CNSLD Revenue =
Var BSG_PSG_Rev = If(and(ISBLANK('Key Measures'[ManagedSrv Billed Revenue])=TRUE(),ISBLANK('Key Measures'[ESG converted Rev])=TRUE()),[BSG_PSG Revenue],if('Key Measures'[ESG converted Rev]<>0, 'Key Measures'[ESG converted Rev]+[BSG_PSG Revenue]))
Var MgdSrv_Rev = If(ISBLANK('Key Measures'[ESG converted Rev])=TRUE(),'Key Measures'[ManagedSrv Billed Revenue],0)
return
BSG_PSG_Rev+MgdSrv_Rev
Solved! Go to Solution.
@ShortyAO 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
@ShortyAO 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
@Greg_Deckler , the second youtube video really helped with the summary table visual you refrenced. I was able to get my total to sum correctly. I am not sure what I was doing wrong with the DAX adding the hasonefilter that was not working for me. Thank you very much for sharing the links!!!
@Greg_Deckler Thank you for the link. I have actually used your post from 2016 in another situation and worked great. However, for some reason here when I use the HASONEFILTER in the following format my total ends up being zero. Any thoughts on what I am doing wrong?
@ShortyAO I will defer this to @Greg_Deckler and he can assist you with this. Cheers!!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
User | Count |
---|---|
102 | |
92 | |
85 | |
78 | |
71 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |