Forum Discussion
DAX Calculation Aggregation not working
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 |
Anonymous
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
3 Replies
Anonymous
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
Anonymous
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.