Forum Discussion
Subtract rows based on columns that have same values
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!
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
3 Replies
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
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?
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