cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GTPowerBIUser Regular Visitor
Regular Visitor

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 IDLevel 3 Project SegmentMain table - Slicer on Level 3 Project Segment
10ABAA.00.00110ABAA.00.001 
10ABAA.00.001.00010ABAA.00.001 
10ABAA.00.001.00110ABAA.00.001 
10ABAA.00.001.99810ABAA.00.001 
   
Funding Table  
Project IDFunding1:1 Join on Project ID to Project Table
10ABAA.00.001100,000 
   
Revenue Table  
Project IDRevenue1:1 Join on Project ID to Project Table
10ABAA.00.001.00010,000 
10ABAA.00.001.00110,000 
10ABAA.00.001.99810,000 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
kcantor Super Contributor
Super Contributor

Re: DAX Calculation Aggregation not working

@GTPowerBIUser 

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

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

3 REPLIES 3
Highlighted
kcantor Super Contributor
Super Contributor

Re: DAX Calculation Aggregation not working

@GTPowerBIUser 

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

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

GTPowerBIUser Regular Visitor
Regular Visitor

Re: DAX Calculation Aggregation not working

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 Super Contributor
Super Contributor

Re: DAX Calculation Aggregation not working

@GTPowerBIUser 

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.



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 488 members 4,285 guests
Please welcome our newest community members: