Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Total of a measure based on subtract of 2 columsn in different related tables

What i have is following data model.

On a certain selected date i have a personnelbudget (field BudgetFTE in vwBudget) and some employees that are working within a company on that data (field No_FTE in vwBezetting)

 

Capture2.PNG

 
 

Now i want to display sum(BudgetFTE - sum(No_FTE) where the difference is > 0 and sum(BudgetFTE - sum(No_FTE) where the difference < 0 in different columns.

This works on level but the total is not there or is incorrect.

 

How can i calculate the correct total per column?

 

See below 

Capture.PNG

1 REPLY 1
kentyler
Solution Sage
Solution Sage

What is happening here is that DAX does not compute the 'total' by adding the values in the columns, the measures that are giving you the values in the columns are just executed again for the cells in the "total" row, but they do not have the filter context that includes the values in the columns that they need to calculate correctly.

You might be able to fix this by just using SUMX instead of SUM.

SUMX is an interator that will interate over all the lines in the current filter context. When the measure is executing in the "total" row it will have a filter context that includes all the rows visible in the current filter context. So it should total up all their values correctly.

When the measure is executing in one of the rows of the table, its filter context only includes the values for that row so the values it calculates reflect only that row. 





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors