Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
Hi @zindagi414
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
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
Hi @zindagi414
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
54 | |
21 | |
12 | |
11 |