Forum Discussion

Decal's avatar
Decal
Icon for Helper I rankHelper I
2 years ago
Solved

DAX Measure to Average Terminations by Month

Seems like this should be simple, but I'm having a heck of a time.

 

I have an Employee table (dimWorker) with a field [Term Date}.  I have a Date table (dimDate) with the date and the [Month Name].  They are propertly joined from dimWorker[Term Date] to dimDate[Date].

 

The business purpose is to show on average how many leave/terminate by month using line/bar chart to assess against a retention goal.  The line chart will show [Month Name] on the x-axis and average terminations in the y-axis.

 

When I drop my terminations measure (see below) on the line chart with my visual filter selecting 2021, 2022, and 2023 I get an extremely high number (633).  The january total should be 68 (average of 22.67) - jan 21 = 18; jan 22 = 24; jan 23 = 26:

Terminations (Dynamic) =
VAR _bop = MIN(dimDate[Date])
VAR _eop = MAX(dimDate[Date])
VAR _terms =
CALCULATE(
    DISTINCTCOUNT(dimWorker[EEID])+0,
    FILTER(dimWorker,
    (dimWorker[Term Date]>=_bop && dimWorker[Term Date]<=_eop)
    )
)
RETURN _terms
 
Hopefully I've provided enough information for someone to help.
 
Cheers!
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi, Decal 

    Thank you very much for your reply, as well as the sample data provided. Here's the sample data I used:

    Here's your DAX expression:

    Based on this, I built another DAX expression as shown in the following image:

    Terminations =
IF (
    ISINSCOPE ( dimDate[Date].[Year] ),
    [Terminations (Dynamic)],
    SUMX ( ALLSELECTED ( 'dimDate'[Date].[Year] ), [Terminations (Dynamic)] )
)

    When I select the corresponding year in the slicer, total updates correctly:

    I've provided the PBIX file used this time below.

     

     

     

     

    How to Get Your Question Answered Quickly

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

    Best Regards

    Jianpeng Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • Decal's avatar
    Decal
    Icon for Helper I rankHelper I
    2 years ago

    This visual might help you assess what's going on.  You can see the right assignment to the month name, but the total jumps way up at the bottom.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, Decal 

    Based on the image you provided, it looks like total is not showing up correctly. I guess your DAX expression might not properly exclude blank lines from this visual. I'm not very clear of the data in your picture which 633 is filtered.

    Fixing Incorrect Totals in DAX • My Online Training Hub

    In this article, a workaround for measure to be incorrect in the total matrix is proposed: use the SUMX function. Similar to this are:
    Solved: Getting incorrect totals on matrix table - Microsoft Fabric Community

    You can also use the summarize,summarizecolumn functions to solve this problem of incorrect totals.

    Power BI: Totals Incorrect and how to Fix it - Finance BI (finance-bi.com)

    In addition, you can refer the following links to try to solve your problem...

    Why Your Total Is Incorrect In Power BI - The Key DAX Concept To Understand

    Dax for Power BI: Fixing Incorrect Measure Totals

    If you can provide some sample data that does not contain privacy, it will help you solve the problem at hand.

     

     

     

    How to Get Your Question Answered Quickly

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

    Best Regards

    Jianpeng Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • Decal's avatar
      Decal
      Icon for Helper I rankHelper I
      2 years ago

      I tried the SUMX solution with the temporary table and it gave me the same result.

       

      Here is sample data.

      EEIDTerm Date
      11/24/2023
      21/31/2022
      31/25/2021
      41/5/2021
      51/7/2022
      61/6/2023
      71/18/2023
      81/18/2023
      91/31/2023
      101/1/2022
      111/10/2022
      121/31/2022
      131/27/2023
      141/26/2023
      151/19/2022
      161/5/2023
      171/15/2021
      181/10/2021
      191/12/2021
      201/1/2021
      211/15/2021
      221/15/2021
      231/10/2021
      241/5/2021
      251/8/2021
      261/10/2022
      271/1/2022
      281/12/2021
      291/29/2022
      301/30/2022
      311/21/2022
      321/26/2022
      331/30/2021
      341/30/2021
      351/15/2021
      361/6/2021
      371/7/2022
      381/14/2022
      391/17/2022
      401/29/2021
      411/2/2023
      421/13/2023
      431/6/2023
      441/7/2023
      451/31/2023
      461/29/2023
      471/24/2023
      481/1/2023
      491/20/2023
      501/25/2022
      511/25/2022
      521/11/2023
      531/11/2022
      541/7/2022
      551/20/2023
      561/6/2023
      571/12/2023
      581/10/2022
      591/18/2023
      601/24/2022
      611/2/2023
      621/7/2022
      631/28/2022
      641/27/2023
      651/10/2023
      661/18/2021
      671/26/2022
      681/20/2023

       

      The date table is in a separate table with the date and the month names.

       

      Greatly appreciate the help.  Very confused by this.

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi, Decal 

        Thank you very much for your reply, as well as the sample data provided. Here's the sample data I used:

        Here's your DAX expression:

        Based on this, I built another DAX expression as shown in the following image:

        Terminations =
IF (
    ISINSCOPE ( dimDate[Date].[Year] ),
    [Terminations (Dynamic)],
    SUMX ( ALLSELECTED ( 'dimDate'[Date].[Year] ), [Terminations (Dynamic)] )
)

        When I select the corresponding year in the slicer, total updates correctly:

        I've provided the PBIX file used this time below.

         

         

         

         

        How to Get Your Question Answered Quickly

        If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

        Best Regards

        Jianpeng Li

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.