cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
pizi Frequent Visitor
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 Smiley Sad
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

Accepted Solutions
Phil_Seamark Super Contributor
Super Contributor

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

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!

2 REPLIES 2
Phil_Seamark Super Contributor
Super Contributor

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

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!

pizi Frequent Visitor
Frequent Visitor

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

Hi @Phil_Seamark

It looks like you 've solved my problem! Smiley Happy

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! Smiley Happy