Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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 differenc...
Need Help
Show and Tell
  • v-echaithra's avatar
    v-echaithra
    1 year ago

    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.


v-echaithra's avatar
v-echaithra
Icon for Community Support rankCommunity Support
1 year ago

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.


  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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. 

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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.