Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Help with Cumlative Total please

Hi 

I am trying to write a dax to create a cummlative total but is isnt working - can anyone help please?

 

I have the following table  - in the cumlative column I want it to total up as I have manually inserted below.

 

The total Headcount measure is  as follows 

 

Total Headcount = DISTINCTCOUNT('MASTER FILE'[EMP ID])
 
In the Table "Master FIle" there is a ID number which it is doing a disctinct count on and returning this against a date column called "report month"
 
I have tried a running total Quick measure and it just isnt totalling as I want it - any help please?

 

Report MonthTotal HeadcountCumlative Total

01 April 2023

18931893
01 May 202319133806
01 June 202319115717
01 July 202318977614

 

I have tried this 

 

Running total = CALCULATE(sumx(values('Calendar'[Report Month]),[Total Headcount]),Filter(All('Calendar'),'Calendar'[Report Month] <= max('Calendar'[Report Month])))
 
But the output is this?
 
Report MonthTotal HeadcountRunning total
01/04/2023189318317
01/05/2023191320230
01/06/2023191122141
01/07/2023189724038
01/08/2023186625904
01/09/2023186227766
01/10/2023183129597

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    2 years ago

    Hi, I am not sure how your datamodel looks like, but please try something like below whether it suits your requirement.

     

    Running total =
SUMX (
    ADDCOLUMNS (
        SUMMARIZE (
            FILTER (
                ALLSELECTED ( 'Calendar' ),
                'Calendar'[Report Month] <= MAX ( 'Calendar'[Report Month] )
            ),
            'Calendar'[Report Month]
        ),
        "@result", [Total Headcount]
    ),
    [@result]
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

    Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or if you are still confused about it, please feel free to let me know.

     

    Best regards,
    Community Support Team_Binbin Yu
    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

    Thank you all for you help - I managed to get it working by using the following:- 

     

    SUMX(FILTER(ALLSELECTED('Calendar'[Report Month]), 'Calendar'[Report Month]<= MAX('Calendar'[Report Month])),CALCULATE(DISTINCTCOUNT('MASTER FILE'[Headcount])))