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

[pizi]

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:

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:

 

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.

[Phil_Seamark · Accepted solution]

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.

[pizi]

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! 🙂