Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pizi
Frequent Visitor

Power BI (DAX) Problem with total during calculating Cumulative Total

Hi.
I've got a problem with my cumulative measure.
I'll try to describe shortly report I have to deliver for HR department.
The aim of the report is to deliver average of workers' seniority over time.
I've got data generated monthly from the source system (every end of the month).
You can download PBIX file from this location: https://1drv.ms/u/s!Aviay2adr4uzcJ1P5MO6g-DqeiA

Please have a look below:

CumulativeFlatData.PNG
Description of columns:
PersonId - unique identifier of person
PersonName - first and last name of person
Date - date of observations
Seniority - for each month there is value of 1 (except 31.01.2017, when I've got cumulated Seniority from the beginning of the company to 31.01.2017)

 

Formula to calculate average is quite easy. For a specific date it's a cummulated seniority divided by current month number of workers.
Let's start with denominator. It's quite easy to calculate:

Number of workers = DISTINCTCOUNT(HR[PersonId])

It works fine.

 

As a second step I would like to calculate nominator.
I've created measure based on an example from https://www.daxpatterns.com/cumulative-total/ :

Cumulated Seniority = 
CALCULATE (
SUM ( HR[Seniority] );
FILTER (
ALL ( HR[Date] );
HR[Date] <= MAX( HR[Date]) )
)

It works fine for each rows (each workers), but the problem occurs on total as you can see on the screenshot below:

 

 PowerBICumulativeProblem.png

I wanted to make some workarounds but without any success 😞
I was trying to find some additional info on the Internet about the problem, but without luck.
Could anyone help me in that case?
Please let me know if you need any more details.
I will appreciate any help.


PS. I was trying to highlight my problem and I've simplified the example a little bit.
A real case is a more complicated.
There are also other pieces on information like: location, name of the department, etc.
It will be perfect if total could behave correctly in all cases.

1 ACCEPTED SOLUTION
Phil_Seamark
Employee
Employee

HI @pizi

 

Have you tried using SUMX?

 

I created a new calculated measure as follows

 

Measure = 
    IF(
        ISFILTERED('HR'[PersonName]),
        -- THEN --
        [Cumulated Seniority],
        -- ELSE --
       SUMX(VALUES('HR'[PersonName]),[Cumulated Seniority])
        )

and then put that into the matrix in place of your [Cumulative Seniority] measure and got the following.  I have attached a PBIX file.

 

 

image.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

2 REPLIES 2
Phil_Seamark
Employee
Employee

HI @pizi

 

Have you tried using SUMX?

 

I created a new calculated measure as follows

 

Measure = 
    IF(
        ISFILTERED('HR'[PersonName]),
        -- THEN --
        [Cumulated Seniority],
        -- ELSE --
       SUMX(VALUES('HR'[PersonName]),[Cumulated Seniority])
        )

and then put that into the matrix in place of your [Cumulative Seniority] measure and got the following.  I have attached a PBIX file.

 

 

image.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hi @Phil_Seamark

It looks like you 've solved my problem! 🙂

Thank you so much!

 

I was thinking about using SUMX but the key was to use ISFILTERED() funcion.

I'm really happy, have a nice day! 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.