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 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?
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
)
6 Replies
- tamerj1
Community Champion
Hi Anonymous
what determines the order of CATEGORIES? What is the DAX code of the Current W1 measure?
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.- tamerj1
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])
