The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello PowerBI Community -
I have been going around in circles and may just be overthinking this issue. I come from a relationship SQL world so still struggling with DAX a bit. I have a matrix with a few measures and would like to calculate a 'difference from overall' for the 'Payer Group' below.
Here are my definitions:
Calculation 2017 = CALCULATE([Yield 365 All], year(Discharge[Discharge Date]) = 2017)
2017 Overall Avg = CALCULATE([Calculation 2017],ALL(Discharge),Discharge[Discharge Date].[Year]=2017)
Difference 2017 from Avg = [Calculation 2017] - [2017 Overall Avg]
This problem is with the '2017 Overall Avg' measure - it should be at 103.41%, just like the footer of the matrix but it's showing 89.35%.
How can I calculate the overall total for the year, and reuse it to do the difference calculation by the category shown?
Thank you!
The following might work:
2017 Overall Avg = CALCULATE([Calculation 2017],ALLEXCEPT(Discharge,Discharge[Discharge Date]))
If there are additional filters you want to maintain, just add column to ALLEXCEPT function.
Alternatively, you can use ALL but just on the Payer Group column.
Thank you MarkLaf.
The ALLEXCEPT on [Discharge Date] revision gave the same 89.3% result.
But then I tried the following at your suggestion:
2017 Overall Avg = CALCULATE([Calculation 2017],ALL((Discharge[Payer Group])))
And now I'm showing 103.55%. Still not the 103.41% but ALOT closer and maybe just a precision issue.
Thanks!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
16 | |
12 | |
3 | |
2 | |
2 |