Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I'm getting some strange behavior with the below calculation. I'm wondering if anyone has a work around or better function.
Measure Calculation: Burn Total = Sum(Funding) - Sum(Revenue) = -30,000
Its as though its taking 0 - 30,000 = -30,000 instead of 100,000 - sum(10,000+10,000+10,000) = 70,000
Slicer on Level 3 Project Segment: 10ABAA.00.001
Project Table | Comments | |
Project ID | Level 3 Project Segment | Main table - Slicer on Level 3 Project Segment |
10ABAA.00.001 | 10ABAA.00.001 | |
10ABAA.00.001.000 | 10ABAA.00.001 | |
10ABAA.00.001.001 | 10ABAA.00.001 | |
10ABAA.00.001.998 | 10ABAA.00.001 | |
Funding Table | ||
Project ID | Funding | 1:1 Join on Project ID to Project Table |
10ABAA.00.001 | 100,000 | |
Revenue Table | ||
Project ID | Revenue | 1:1 Join on Project ID to Project Table |
10ABAA.00.001.000 | 10,000 | |
10ABAA.00.001.001 | 10,000 | |
10ABAA.00.001.998 | 10,000 |
Solved! Go to Solution.
Anytime I have a complex measure that does not behave as I expect, I break it into separate parts.
Funding Sum = Sum(Funding)
Revenue Sum = Sum(Revenue)
Burn Total = [Funding Sum]-[Revenue Sum]
Drop them all into a table and see where the breakage occurs. Fix the individual calculation that isn't working appropriately. Keep in mind that measure themselves do not take up room in the model and only calculated as used. If the measures work individually, build them into one useing VAR.
Burn Total =
VAR FundingSum = Sum(Funding)
VAR RevenueSum = Sum(Revenue)
RETURN
FundingSum - RevenueSum
Proud to be a Super User!
Anytime I have a complex measure that does not behave as I expect, I break it into separate parts.
Funding Sum = Sum(Funding)
Revenue Sum = Sum(Revenue)
Burn Total = [Funding Sum]-[Revenue Sum]
Drop them all into a table and see where the breakage occurs. Fix the individual calculation that isn't working appropriately. Keep in mind that measure themselves do not take up room in the model and only calculated as used. If the measures work individually, build them into one useing VAR.
Burn Total =
VAR FundingSum = Sum(Funding)
VAR RevenueSum = Sum(Revenue)
RETURN
FundingSum - RevenueSum
Proud to be a Super User!
For prosterity. I used your troubleshooting method and realized it was actually an interaction in another slicer that technically shouldnt have had an impact, once that was turned off everything worked. Thanks again!
And that situation is exactly why I still use that troubleshooting method. Rob Collie taught me that whole of any calculation is the sum of its parts and to inspect what I expect.
Happy your found your issue.
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
78 | |
75 | |
50 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |