Calculated Column - cumulative sum using filter and ranked column

Question

Hello,&nbsp; I'm arelatively new to PBI and I need to create a calculated column that accumulates Net Sales based on the Products Price ranking (note multiple products appear in prod column).&nbsp; &nbsp;The desired result is in the last column:Product NumberProd Ranked by PriceNet PriceNet Sales(EXPECTED RESULT) Cumulative RevenueCat&nbsp;1&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7.00Cat&nbsp;2&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 13.00Cat&nbsp;3&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 18.00Cat&nbsp;4&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 22.00Dog1&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3.00Dog2&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1.00&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4.00&nbsp;&nbsp;My current DAX logic just returns the net sales value at each ranking and does not aggregate the sales.&nbsp; &nbsp;Any help would be greatly appreciated.&nbsp; Thank you!

anonymous · Accepted Answer

Hi&nbsp;JeanMariePBI&nbsp;,
&nbsp;
You could use ALLEXCEPT() funtion and EARLIER() function. Check the formula.
Column =
CALCULATE (
    SUM ( 'Table'[Net Sales] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Product Number] ),
        'Table'[Prod Ranked by Price] &lt;= EARLIER ( 'Table'[Prod Ranked by Price] )
    )
)

&nbsp;
&nbsp;
Best Regards,
Jay

Product Number	Prod Ranked by Price	Net Price	Net Sales	(EXPECTED RESULT) Cumulative Revenue
Cat	1	$ 7.00	$ 7.00	$ 7.00
Cat	2	$ 6.00	$ 6.00	$ 13.00
Cat	3	$ 5.00	$ 5.00	$ 18.00
Cat	4	$ 4.00	$ 4.00	$ 22.00
Dog	1	$ 3.00	$ 3.00	$ 3.00
Dog	2	$ 1.00	$ 1.00	$ 4.00

Forum Discussion

Calculated Column - cumulative sum using filter and ranked column

2 Replies

Related content

Cumulative calculation with Calculated column

Cumulative Total in Calculated Column

TMDL - calculated column

Cumulative Total

Direct Lake Model - Calculated Column/Tables