Difference between 2 rows using first row fixed

[gonnelli]

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:

.AVG_PRICE = CALCULATE(AVERAGE(FORMULARIO_RESPOSTAS[DES_RESPOSTA_QTD]);FORMULARIO_RESPOSTAS[DES_RESPOSTA_QTD] > 0)

Anyone knows how can I get this?

Thanks!

[v-frfei-msft]

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

[Ashish_Mathur]

Hi,

Do you want to take the difference between the price in the current row and the row with the minumum price?

[gonnelli]

Hi @Ashish_Mathur!

I need to calculate always the first rows and another rows, but the first rows can be changed by filter.

[Anonymous]

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.

[gonnelli]

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.

[v-frfei-msft]

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

[v-frfei-msft · Accepted solution]

Hi @gonnelli,

Does that make sense? If so, kindly mark my answer as a solution to close the case.

Regards,
Frank