- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

# Difference between 2 rows using first row fixed

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Thursday - last edited Friday

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:

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

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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"))

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Thursday

Hi,

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Thursday

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

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

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Friday - last edited Friday

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.

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

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Friday - last edited Friday

Hi @Ashish_Mathur!

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