Forum Discussion
Create a measure for relative error with values in same column, for different categories
Hello, I need help with a measure for a Relative Error
%Prediction could be created with method A or B and there's a Base Method. So, for month 1,2, and son on, i need relative error between method A and Base (PRediction with method A - Prediction with Base method) / Prediction with Base method.
For january and method A: (20%-22%)/22% = -9,1%
For method B, same thing: (Prediction with method B - Prediction with base method) / Prediction with base method
so for january and method B: (21%-22%)/22% = -4,5%
Relative error for base method shoud be Zero or Null, it would be plotted later.
How can i create this with DAX? i imagine i must create some sort of index. Any guide to achieve this? Many thanks in advance as usually
| Date / Month | Method | % Prediction | %Relative Error (desire result) |
| 01-01-2022 | A | 20% | -9,1% |
| 01-02-2022 | A | 23% | -4,2% |
| 01-03-2022 | A | 26% | -3,7% |
| 01-01-2022 | B | 21% | -4,5% |
| 01-02-2022 | B | 22% | -8,3% |
| 01-03-2022 | B | 28% | 3,7% |
| 01-01-2022 | Base | 22% | - |
| 01-02-2022 | Base | 24% | - |
| 01-03-2022 | Base | 27% | - |
Hi Anonymous
Thanks for reaching out to us.
try this measure
Relative Error = var _base= MAXX(FILTER(ALL('Table'),'Table'[Date / Month]=MIN('Table'[Date / Month]) && 'Table'[Method]="Base"),[% Prediction]) return ([% Prediction]-_base)/_baseBest Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
v-xiaotangCommunity Support
Hi Anonymous
Thanks for reaching out to us.
try this measure
Relative Error = var _base= MAXX(FILTER(ALL('Table'),'Table'[Date / Month]=MIN('Table'[Date / Month]) && 'Table'[Method]="Base"),[% Prediction]) return ([% Prediction]-_base)/_baseBest Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Hi! sorry to ask again, it worked, i was just wondering if you can explain what the formula is doing?. I'm not understanding logic behind DAX and i'd like to improve my reports by understanding it better.
What does MAXX and MIN like in few words, i get that you filtered BASE but i dont get that MAXX and min part.
Thank you