Forum Discussion
3 Connected Tables and adding a measure
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.
Hi Anonymous ,
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.
4 Replies
v-echaithraCommunity Support
Hi Anonymous ,
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.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 columnWhen I just add the equation without any filters, it adds department rows of the same value.