cancel
Showing results for
Did you mean:
Frequent Visitor

## 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:

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

Anyone knows how can I get this?

Thanks!
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Difference between 2 rows using first row fixed

Hi @gonnelli,

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

Regards,
Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 REPLIES 7
Frequent Visitor

## Re: Difference between 2 rows using first row fixed

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.
Super User

## Re: Difference between 2 rows using first row fixed

Hi,

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

Community Support Team

## Re: Difference between 2 rows using first row fixed

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Difference between 2 rows using first row fixed

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.

Highlighted
Frequent Visitor

## Re: Difference between 2 rows using first row fixed

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

Community Support Team

## Re: Difference between 2 rows using first row fixed

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team

## Re: Difference between 2 rows using first row fixed

Hi @gonnelli,

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

Regards,
Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 55 members 1,268 guests
Recent signins: