Forum Discussion
Difference between 2 rows using first row fixed
Hello!
I need to calculate the difference between two rows in a matrix table in Power BI, but I need to keep the first row as reference, like this:
EDIT: The first row (Model "A") can be changed by filter, so I need to use always the first row, not the model as reference.
| model | price | diff | explanation |
| A | 1499 | 0 | - |
| B | 1949 | 450 | EXP.: MODEL B - MODEL A |
| C | 1799 | 300 | EXP.: MODEL C - MODEL A |
| D | 2699 | 1200 | EXP.: MODEL D - MODEL A |
I used a metric to calculate this values:
Hi gonnelli,
Does that make sense? If so, kindly mark my answer as a solution to close the case.
Regards,
Frank
7 Replies
Have you tried using the LOOKUPVALUE function? You can use this to return the value for Model A in whatever column you need to reference.
In your example below, the formula would look something like (I'm using "Table1" as the table where your values are stored in this example):
diff = CALCULATE(AVERAGE(Table1[price])-LOOKUPVALUE(Table1[price],Table1[model],"A"))
Put this measure as the diff column in your matrix table visual and you should get the desired results.Hello v-frfei-msft
In my case, I can't use lookupvalues because the first "model" can be changed by filters.
I need to calculate always the difference between the first row with another rows.
Hi gonnelli,
I made an update of the measure.
diffm = MAX(Table1[price])-CALCULATE(SUM(Table1[price]),FILTER(ALLSELECTED(Table1),Table1[model]=MINX(ALLSELECTED(Table1),Table1[model])))
For more details, please check the pbix as attached.
Regards,
Frank
Hi gonnelli,
Two ways you can choose.
1. To create a calculated column.
diffc = Table1[price]-CALCULATE(SUM(Table1[price]),FILTER(ALL(Table1),Table1[model]="A"))
2. To create a measure.
diffm = MAX(Table1[price])-CALCULATE(SUM(Table1[price]),FILTER(ALL(Table1),Table1[model]="A"))
For more details, please check the pbix as attached.
Regards,
Frank
Hi,
Do you want to take the difference between the price in the current row and the row with the minumum price?
Hi Ashish_Mathur!
I need to calculate always the first rows and another rows, but the first rows can be changed by filter.
