Forum Discussion
Need Delta as a column
| Item | Sore | Rank | Delta |
| Item1 | 100 | 1 | 5 |
| Item1 | 100 | 1 | 5 |
| Item1 | 95 | 2 | 15 |
| Item1 | 80 | 3 | 20 |
| Item1 | 100 | 1 | 5 |
| Item1 | 60 | 4 | 10 |
| Item1 | 50 | 5 | 0 |
| Item1 | 50 | 5 | 0 |
| Item2 | 100 | 1 | 20 |
| Item2 | 80 | 2 | 10 |
| Item2 | 70 | 3 | 30 |
| Item2 | 80 | 2 | 10 |
| Item2 | 40 | 4 | 0 |
| Item2 | 80 | 2 | 10 |
| Item2 | 40 | 4 | 0 |
HI All,
I m new to Power BI, I have score for every time, based on that I have calucluated by rank of score for that item.
now I oking for the Delta of score based on my Rank
Consider above table.
Item1 Rank1 score is 100, 2nd is 95, I need 5 as Delta (100-95) as result.
Appreciate your support here. Thanks 🙂
Hi KiranGupta15 ,
You could use earlier() function, check the measure below.
Column = var a = CALCULATE(AVERAGE('Table'[Sore]),FILTER(ALLEXCEPT('Table','Table'[Item]),'Table'[Rank]=EARLIER('Table'[Rank])+1)) return IF(ISBLANK(a),0,'Table'[Sore]-a)Best Regards,
Jay
2 Replies
If you want a column, you could start with this,
on each row , filter the table to get the score (you have to use an aggregate so min,or max or average should work) of the same item where the rank is +1.
Also, I think almost all the Item2 Delta in the table provided are incorrect.
Hi KiranGupta15 ,
You could use earlier() function, check the measure below.
Column = var a = CALCULATE(AVERAGE('Table'[Sore]),FILTER(ALLEXCEPT('Table','Table'[Item]),'Table'[Rank]=EARLIER('Table'[Rank])+1)) return IF(ISBLANK(a),0,'Table'[Sore]-a)Best Regards,
Jay
