Calculated Column - cumulative sum using filter and ranked column

[JeanMariePBI]

Hello,  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).   The desired result is in the last column:

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

My current DAX logic just returns the net sales value at each ranking and does not aggregate the sales.   Any help would be greatly appreciated.  Thank you!

[Anonymous · Accepted solution]

Hi @JeanMariePBI ,

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] <= EARLIER ( 'Table'[Prod Ranked by Price] )
    )
)

 

Best Regards,

Jay

[MFelix]

Hi @JeanMariePBI ,

Try the following code:

Cumulative =
CALCULATE (
    SUM ( 'Table'[Net Sales] ),
    ALL ( 'Table' ),
    'Table'[Product Number] = EARLIER ( 'Table'[Product Number] )
        && 'Table'[Prod Ranked by Price]
            <= EARLIER ( 'Table'[Prod Ranked by Price] )
)