Subtract rows based on columns that have same values

[Anonymous]

Here is what I would like to do. For example, lets say I have a chart as below:

Food Type		Store		Type		Value
Sandwich		Subway		Type1		7
Sandwich		Subway		Type2		5
Taco		Qdoba		Type1		10
Taco		Qdoba		Type2		5
Taco		Qdoba		Type3		6

I would like to create a new column in this row. For each row, I want to Filter the SAME "Food Type" and "Store" and take the "Value" difference between "Type #" and "Type 1. 

In the table below, I have added the "Desired Value" I'd like to see being done. I also added a couple of Columns ("Row" and "Formula" to show how I'd get to the "Desired Value"

Row #

Food Type

Store

Type

Value

Desire Values

Formula

1

Sandwich

Subway

Type1

7

0

Row 1 - Row 1

2

Sandwich

Subway

Type2

5

2

Row 1 - Row 2

3

Taco

Qdoba

Type1

10

0

Row 3 - Row 3

4

Taco

Qdoba

Type2

5

5

Row 3 - Row 4

5

Taco

Qdoba

Type3

6

4

Row 3 - Row 5

In advance, any assistance will be greatly appreciated. Thanks! 

[AlB]

@Anonymous 

Good question. The filters in the calculate will filter the table down to one single row. So the MAX is not strictly needed. You could just as well use DISTINCT ( ) 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB · Accepted solution]

Hi @Anonymous 

If it's a calculated column that you are after:

New column =
Table1[Value]
    - CALCULATE (
        MAX ( Table1[Value] ),
        ALLEXCEPT ( Table1, Table1[Food Type], Table1[Store] ),
        Table1[Type] = "Type1"
    )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

@AlB  - thank you! This works as expected!

For my own knowledge/learning, can you clarify why the MAX function is needed here? It works with MAX, but why does the code need to look at the maximum value?