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.
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.
Hi @gonnelli,
Does that make sense? If so, kindly mark my answer as a solution to close the case.
Regards,
Frank
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
Hi,
Do you want to take the difference between the price in the current row and the row with the minumum price?
Hi @Ashish_Mathur!
I need to calculate always the first rows and another rows, but the first rows can be changed by filter.
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"))
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.
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
Hi @gonnelli,
Does that make sense? If so, kindly mark my answer as a solution to close the case.
Regards,
Frank
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 |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |