topic Re: Help with Cumlative Total please in DAX Commands and Tips

Help with Cumlative Total please

Anonymous — Thu, 16 Nov 2023 16:48:56 GMT

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 Month	Total Headcount	Cumlative Total
01 April 2023	1893	1893
01 May 2023	1913	3806
01 June 2023	1911	5717
01 July 2023	1897	7614

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 Month	Total Headcount	Running total
01/04/2023	1893	18317
01/05/2023	1913	20230
01/06/2023	1911	22141
01/07/2023	1897	24038
01/08/2023	1866	25904
01/09/2023	1862	27766
01/10/2023	1831	29597

Re: Help with Cumlative Total please

Jihwan_Kim — Fri, 17 Nov 2023 04:59:37 GMT

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

Re: Help with Cumlative Total please

Anonymous — Mon, 20 Nov 2023 06:20:54 GMT

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.

Re: Help with Cumlative Total please

Anonymous — Tue, 21 Nov 2023 09:26:21 GMT

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