topic Re: Cummulative sum in a column in DAX Commands and Tips

Cummulative sum in a column

Anonymous — Mon, 10 Oct 2022 11:57:01 GMT

Hi,
1) I have Grand Total column as below, from that i need to get % column value listed below.
Grand Total 656,306
to get 30% i need to do 197,829/656,306 = 30%
to get 13% i need to do 84,635/656,306 = 13% ....so on

2)How can i get the cummulative sum from the %column?
1st row values remains same as 30%.
2nd row column value - 30%+13% = 43%
3rd row column value - 30+11+13 = 54% etc...

Please advise

Grand Total656,306

%	bleep
197,829	30%	30%
84,635	13%	43%
69,128	11%	54%
51,932	8%	61%
45,210	7%	68%
41,335	6%	75%
35,815	5%	80%
26,962	4%	84%
13,781	2%	86%
13,417	2%	88%
9,752	1%	90%
9,750	1%	91%
9,456	1%	93%
9,378	1%	94%
9,111	1%	96%
8,394	1%	97%
5,577	1%	98%
5,191	1%	99%
4,373	1%	99%
2,976	0%	100%
1,824	0%	100%
480	0%	100%
656,306

Re: Cummulative sum in a column

Jihwan_Kim — Mon, 10 Oct 2022 12:51:27 GMT

Hi,

Please check the below picture and the attached pbix file.

Percentage measure: = VAR _qty = SUM ( Data[Quantity] ) VAR _allqty = CALCULATE ( SUM ( Data[Quantity] ), REMOVEFILTERS () ) RETURN DIVIDE ( _qty, _allqty )

Cumulate percentage measure: = VAR _qty = SUM ( Data[Quantity] ) VAR _cumulate = SUMX ( FILTER ( ALL ( Data[Quantity] ), Data[Quantity] >= _qty ), [Percentage measure:] ) RETURN _cumulate

Re: Cummulative sum in a column

Anonymous — Mon, 10 Oct 2022 13:25:12 GMT

Thank you so much for the Quick Response
Cumulative sum is not working for me
if you can see Percentage % and Cummative Percentage values both are same.
I have used the Measures which you shared. but values are same
Please help ?

Re: Cummulative sum in a column

Jihwan_Kim — Mon, 10 Oct 2022 13:51:29 GMT

Hi,

Could you please share your sample pbix file's link here? And then I can try to look into it to come up with a more accurate solution.

Thanks.

Re: Cummulative sum in a column

Anonymous — Mon, 10 Oct 2022 14:00:32 GMT

Sorry i cant share the pbix file it has huge sensitive data. But i can share the screenshot of the measure which i am using.

Re: Cummulative sum in a column

Jihwan_Kim — Mon, 10 Oct 2022 14:14:42 GMT

Hi,

I cannot know the column name in your visualization that is placed in the left side of it.

Please try something like below.

Cumulate percentage measure: = VAR _percentage = [Percentage measure:] VAR _cumulate = SUMX ( FILTER ( ALL ( 'TableName'[somethinglikeindexcolumn] ), [Percentage measure:] >= _percentage ), [Percentage measure:] ) RETURN _cumulate

Re: Cummulative sum in a column

Anonymous — Mon, 10 Oct 2022 14:19:41 GMT

Hi,
I am using the Country in left column of the visual

Re: Cummulative sum in a column

Jihwan_Kim — Mon, 10 Oct 2022 14:22:01 GMT

Please try the below whether it suits your requirememt.

Cumulate percentage measure: = VAR _percentage = [Percentage measure:] VAR _cumulate = SUMX ( FILTER ( ALL ( 'TableName'[country] ), [Percentage measure:] >= _percentage ), [Percentage measure:] ) RETURN _cumulate

Re: Cummulative sum in a column

Anonymous — Mon, 10 Oct 2022 14:26:33 GMT

When i use country column in the measure i am getting like below cummulative is not coming in correct way. Please see below

Cumulate percentage measure: =

VAR _percentage = [Percentage %]

VAR _cumulate =

SUMX (

FILTER (

ALL ( 'DB 15092022_1'[country]),

[Percentage %] >= _percentage

[Percentage %]

)

RETURN

_cumulate

Re: Cummulative sum in a column

Jihwan_Kim — Tue, 11 Oct 2022 03:01:06 GMT

Hi,

May I ask, do you want to cumulate by country code alphabetical order? Or, by QTY descending order?

I think the measure is showing cumulate percentage by QTY descending order.