Forum Discussion
Cumulative substraction over categories
I have a matrix table in PBI and I would like to calculate a kind of cumulative substraction over the categories. The main goal is to achieve the "Expected W-1" field, so in a nutshell: for Category ...
Anonymous
Please try
Count.AllOpportunities.New =
VAR CategoriesAndCounts =
ADDCOLUMNS (
ALLSELECTED ( Category_Table[Category_Order] ),
"@Count",
CALCULATE (
[Count.AllOpportunities],
ALLEXCEPT ( Category_Table, Category_Table[Category_Order] )
)
)
RETURN
SUMX (
VALUES ( Category_Table[Category_Order] ),
VAR CurrentOrder = Category_Table[Category_Order]
VAR CurrentCount = [Count.AllOpportunities]
VAR NextCount =
MAXX (
TOPN (
1,
FILTER ( CategoriesAndCounts, Category_Table[Category_Order] > CurrentOrder ),
Category_Table[Category_Order], ASC
),
[@Count]
)
RETURN
CurrentCount - NextCount
)
Just create the following visual calculation
Expected W-1 = [Current W-1] - NEXT([Current W-1])