3 Connected Tables and adding a measure

[shawnloj]

Hi, 

I have 3 tables that are connected. (One is from a SQL database and two from an excel file. 

Here is the graph its showing. I am trying to add a measure that takes the difference between active staff and budget.

When I add the measure to the graph, it messes up all the numbers. 

[v-echaithra · Accepted solution]

Hi @shawnloj ,

Thank you for reaching out to Microsoft Community.
COUNTA and SUM operate in different table contexts, this results in an incorrect aggregation when used in visuals like matrices. Try this DAX measure:
Difference in Total to Budgeted =
VAR Staffcount = CALCULATE(COUNTA('HR table'[Full Name]))
VAR Budgeted = CALCULATE(SUM('dir or ind'[Budget]))
RETURN
Staffcount - Budgeted

If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

Thanks,
Chaithra E.

[shawnloj]

Thank you @v-echaithra, 

I think I need to change the context of the formula a bit. When I add that field in, it adds morerows to the graph. I tried adding in the filter all, and also tried using Allexcept. It is then showing the correct amount of rows but the numbers are not adding up correctly.  

Here is the formula I tried to use that created the correct row count to match, but the numbers are wrong. It should take the total of open postions + Active count - budget

correct graph row count but wrong numbers under difference1 column

 When I just add the equation without any filters, it adds department rows of the same value. 

[shawnloj]

Turns out the formula is not filtering with the rest of the columns when I use a filter on the same page. Not sure how to fix this issue though. 

[shawnloj]

Here is the measure I used 

Difference in Total to Budgeted = COUNTA('HR table'[Full Name])-(SUM('dir or ind'[Budget]))