Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi! I need to calculate a 'simple' subtraction. I've got a visual table with products, and always the last 12 weeks with the calculate of their regresion in every week, and what i need its to calculate the regresion subtraction of the first and the last week who has any data (not blank).
week its a measure and regresion its a measure as well
Ex of the visual table:
product 12002 // 1st week regresion = 0.166 // [... rest of weeks...] // lastweek regresion= 0.142
what i need:
measure (for every product) = 1st week regresion (not blank) - lastweek regresion (not blank) = 0.024
how can i get this in a measure?
thanks in advance!
Hi again @ivanmoreno
Per my understanding the solution shall be something like this:
First activate the column totals from the format pane.
Then remove the current regression measure from the matrix and add the following measure instead:
=
IF (
HASONEVALUE ( TableName[Week Number] ),
[Regression],
VAR T1 =
ADDCOLUMNS ( VALUES ( TableName[Week Number] ), "@Regression", [Regression] )
VAR T2 =
FILTER ( T1, [@Regression] <> BLANK () )
VAR MaxWeek =
MAXX ( T2, TableName[Week Number] )
VAR MinWeek =
MINX ( T2, TableName[Week Number] )
VAR MaxWWeekValue =
MAXX ( FILTER ( T2, TableName[Week Number] = MaxWeek ), [@Regression] )
VAR MinWWeekValue =
MINX ( FILTER ( T2, TableName[Week Number] = MinWeek ), [@Regression] )
RETURN
MaxWWeekValue - MinWWeekValue
)
Hi @ivanmoreno
what do you mean by "week its a measure"? Isn't it a column? Do you have a date table?
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |