Thursday - last edited Friday
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.
|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:
Thursday - last edited Thursday
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"))
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.
Friday - last edited Friday
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.