DAX Calculation Aggregation not working

[Anonymous]

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

[kcantor · Accepted solution]

@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]

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!

[kcantor]

@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.