- 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

12-06-2018 12:46 PM - edited 12-07-2018 09:02 AM

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:

Solved! Go to Solution.

Accepted Solutions

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

12-30-2018 10:15 PM

Hi @gonnelli,

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

Regards,

Frank

If this post

**helps**, then please consider

**to help the other members find it more quickly.**

*Accept it as the solution*All Replies

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

12-06-2018 05:47 PM - edited 12-06-2018 05:48 PM

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

12-06-2018 07:30 PM

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

12-06-2018 11:35 PM

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

If this post

**helps**, then please consider

**to help the other members find it more quickly.**

*Accept it as the solution*## 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

12-07-2018 03:59 AM - edited 12-07-2018 08:59 AM

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

12-07-2018 08:58 AM - edited 12-07-2018 08:59 AM

Hi @Ashish_Mathur!

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

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

12-23-2018 10:08 PM

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

If this post

**helps**, then please consider

**to help the other members find it more quickly.**

*Accept it as the solution*## 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

12-30-2018 10:15 PM

Hi @gonnelli,

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

Regards,

Frank

If this post

**helps**, then please consider

**to help the other members find it more quickly.**

*Accept it as the solution*