Forum Discussion
Properly calculating cummulative value
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
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 TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
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 TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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.
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)
)
)
