topic Properly calculating cummulative value in DAX Commands and Tips

Properly calculating cummulative value

gumis_rulez — Mon, 09 Sep 2024 14:56:54 GMT

I am struggling with cummulative value when I am creating a summarize table in dax measure:

I am using "others" to calculate the result for all other customers which have rank higher than parameter.

The revenue measure is following:

Revenue = Var OrderOfAll = CALCULATE([Total Sales Dates Range Param.], REMOVEFILTERS(hlpCustomer[customer_name])) RETURN IF( ISINSCOPE(hlpCustomer[customer_name]), Var ProductsToRank = hlpNumber[Number Value] Var IsotherSelected = SELECTEDVALUE(hlpCustomer[customer_name]) = "Others" Var ProductsWithOrderAmt = ADDCOLUMNS(ALLSELECTED(hlpCustomer[customer_name]), "@Amt", [Total Sales Dates Range Param.]) Var Top3Prods = TOPN(ProductsToRank, ProductsWithOrderAmt, [@Amt]) Var OrdersOfTop3 = SUMX(Top3Prods, [@Amt]) Var Result = IF(IsotherSelected, OrderOfAll - OrdersOfTop3, [Total Sales Dates Range Param.]) Return Result, OrderOfAll )

How to properly calculate increasing sum (cummalitive) - red colour on the screenshot?

https://drive.google.com/file/d/1Y936d8gqn4JYdWi8PXyR9tHXqUo6pyJ0/view?usp=sharing

Re: Properly calculating cummulative value

Selva-Salimi — Mon, 09 Sep 2024 15:47:03 GMT

gumis_rulez

you can write it as follows:

calculate (sum(revenue), filter (table, evenue <= earlier (revenue))

If this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

Re: Properly calculating cummulative value

FlipFlop1 — Mon, 09 Sep 2024 15:59:23 GMT

So you can use a quick measure to generate the cumulative total for you: See Quick Measure > Running total.

I think the DAX will look like this for your example:

Sales running total in Customer_name =
CALCULATE(
[Revenue],
FILTER(
ALLSELECTED(),
ISONORAFTER('hlpCustomer'[customer_name], MAX('hlpCustomer'[customer_name]),ASC)
)
)

Re: Properly calculating cummulative value

Anonymous — Tue, 10 Sep 2024 09:59:43 GMT

Thanks for the reply from FlipFlop1 and Selva-Salimi.

Hi gumis_rulez ,

Here is the formula I provided:

MEASURE =
VAR _table =
ADDCOLUMNS (
SUMMARIZE ( ALL ( 'hlpCustomer' ), 'hlpCustomer'[customer_name] ),
"rank", [Rank]
)
VAR _current_rank = [Rank]
RETURN
IF (
ISINSCOPE ( hlpCustomer[customer_name] ),
SUMX ( FILTER ( _table, [rank] <= _current_rank ), [Revenue] )
)

The result is as follows:

Best Regards,
Zhu
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.