topic Cumulative substraction over categories in DAX Commands and Tips

Cumulative substraction over categories

Anonymous — Tue, 11 Mar 2025 17:11:23 GMT

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 1, the Expected W-1 calculation calculated in this way 10119 - 7427 = 2692,

Further information that I am using a simple DAX measure =
Count = Count(fact_table) (this is required because additonal filtering can be applied in the future.

CATEGORIES	Current W-1	Expected W-1	How it is calculated?
Category 1	10119	2692	Category 1 - Category 2
Category 2	7427	3312	Category 2 - Category 3
Category 3	4115	2127	Category 3 - Category 4
Category 4	1988	109	Category 4 - Category 5
Category 5	1879	97	Category 5 - Category 6
Category 6	1782	383	Category 6 - Category 7
Category 7	1399	1399	Category 7
Total	28709	10119

Is there any way to solve this?

Re: Cumulative substraction over categories

tamerj1 — Tue, 11 Mar 2025 17:48:48 GMT

Hi Anonymous

what determines the order of CATEGORIES? What is the DAX code of the Current W1 measure?

Re: Cumulative substraction over categories

Anonymous — Tue, 11 Mar 2025 18:40:41 GMT

The simplified model looks like this:

In a nutshell I am ordering the "Category_label" field with the "Category_order" field with the built in function (under the "Column tools"), the "Category_order" field is integer.

The Current W1 measure looks like this:

Count.AllOpportunities = VAR previousweek = [Date.8to14Days] RETURN CALCULATE(COUNT('fact_table'[UniqueID]),FILTER('fact_table','fact_table'[Event_date]> previousweek))

The previousweek variable returns a single date to determine what is the cut off date.

Re: Cumulative substraction over categories

tamerj1 — Tue, 11 Mar 2025 19:46:49 GMT

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
)

Re: Cumulative substraction over categories

Anonymous — Tue, 11 Mar 2025 19:52:41 GMT

and it worked 😮 thank you so much! but I really need to ask that did you use any kind of help, because that was outstanding

Re: Cumulative substraction over categories

tamerj1 — Tue, 11 Mar 2025 21:32:56 GMT

Anonymous

Definitely no AI. Not even a laptop 😂 I just typed the code using my phone

Re: Cumulative substraction over categories

ahmedashour — Sat, 15 Mar 2025 11:00:14 GMT

Just create the following visual calculation

Expected W-1 = [Current W-1] - NEXT([Current W-1])