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]...
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.

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.