A Measure to Calculate the Cumulative Sum of Another Measure, with Filter on Measure

[ylin88_waters]

Hi

What’s the DAX code to create a measure that calculates the cumulative sum of another measure?

My data is something like following:

Customer	Sales	Rank (measure)	% of Total (measure)	Cumulative % (new measure)
A	1000	1	33.3%	33.3%
B	800	2	26.7%	60.0%
C	500	3	16.7%	76.7%
D	400	4	13.3%	90.0%
E	300	5	10.0%	100.0%

Both “Rank” and “% of Total” are measures. Now I need to create another measure “Cumulative %” to sum up on the measure “% of total”.

Many examples I found are for the calculation of cumulative sum on columns, not measures, or the filter is on the column values. but here it seems I need to filter on a measure (Rank). How do you get that Rank # into your DAX code and then sum up on “% of Total”?

Thanks!

YL

[v-jayw-msft · Accepted solution]

Hi @ylin88_waters ,

Check the formula.

Measure = SUMX(FILTER(ALLSELECTED('Table'),[_rank]<=MAXX('Table',[_rank])),'Table'[_% of Total])

Best Regards,

Jay

[VahidDM]

Hi @ylin88_waters 

Try this:

Cumulative % = 
Var _R = [Rank]
Var _A = SUMMARIZE(all('Table'),'Table'[Customer],'Table'[Sales],"Rank",[Rank])
Var _B = filter(_A,[Rank]<=_R)
Var _C = SUMX(_B,[Sales])
return
_C/CALCULATE(sum('Table'[Sales]),all('Table'))

output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[parry2k]

@ylin88_waters try something like this:

**bleep** Sum = 
VAR __rank = [Rank Measure]
RETURN SUMX ( FILTER( ALL ( 'Table' ), [Rank Measure] <= __rank ), [% Total] )

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[ylin88_waters]

Hi VahidDM & parry2k:

Thanks a lot for your help.

I tried both of your sample codes, but they didn't work. It seems the Dax didn't really capture the Rank (a measure) of current row.  It actually calculated the sum of all rows. It seems I can't upload my testing file here(using 1000 rows of dummy data), so I just put an image of the result table.

Measures:

Sales $ = SUM('Sales_test'[Sales])

====================

Rank =

RANKX( ALLSELECTED(Sales_test[CustomerID]),'Sales_test'[Sales $])

=================

% of Total Orders =

DIVIDE( [Sales $],

CALCULATE( [Sales $],

ALL('Sales_test'[CustomerID])

))

===================

Cumulative % 1 =

VAR myrank = [Rank]

RETURN SUMX ( FILTER( ALL ( 'Sales_test' ), [Rank] <= myrank ), [% of Total Orders] )

 ====================

Cumulative Sales 1 =

VAR __rank = [Rank]

RETURN SUMX ( FILTER( ALL ( 'Sales_test' ), [Rank] <= __rank ), [Sales $] )

==============

Cumulative % 2 =

Var _R = [Rank]

Var _A = SUMMARIZE(all('Sales_test'),'Sales_test'[CustomerID],[Sales],"Rank",[Rank])

Var _B = filter(_A,[Rank]<=_R)

Var _C = SUMX(_B,[Sales $])

return

_C/CALCULATE(sum([Sales]),all('Sales_test'))

 =====================

If you see anything I need to change to make it work, le tme know. Thanks again.