Forum Discussion
formulas between different tables
Hello mat007mat
as you are writing about KPI I assume you need a solution in Power Pivot/DAX. I would prefer combining such tables in Power query and do the final calculation in DAX.
However.. to solve this with DAX I saw no other solution to create a new table out of the forecast-table only using country, type and month. Then adding 3 new columns to calculate forecast, sales and error. the measure uses then sales and error to get the KPI.
Find enclosed the file with my solution
If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun
Jimmy
Hi mat007mat
Where is the sum of absolute errors coming from?
Is it from the values i circled in blue?
But they doesn't equal to example sum of absolute errors(jan: 7+5+19+7).
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Dear Maggie,
the error was calculated as;
| sales | forecast | absolute error | |
| item1 | 3+4 | 5+9 | 7 |
| item2 | 7 | 12 | 5 |
| item3 | 22 | 3 | 19 |
| item4 | 0 | 7 | 7 |
so the KPI would be (7+5+19+7) / (3+4+7+22+0) = 105.5% (and not the 44% that i mistakenly said first - apologies)