Forum Discussion
formulas between different tables
dears,
I would like to get help on following issue - I am combining two tables, one with actuals sales and one with forecasted sales. I would like to build the following KPI: I want to sum up all absolut forecast errors (per item type, not on packaging) for a region, and divide that to the actual sales of that region.
I hae added two tables below to describe the issue
Sales:
sales
forecast:
forecast
the expected outcome would be :
jan feb
Belgium 44% 33%
this is based on the sum of absolute errors (jan: 7+5+19+7) divided by sum of sales (7+7+22) = 44%.
please let me know if above would not be clear,
appreciate your help to formulate above kpi in a dax for power pivot
thanks
Mat
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
7 Replies
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
JimmyThis is great! thanks a lot for your time and effort in this!
I have not build it in my tool yet - but I am sure that the described methodology will do.
formulas that I was not aware of, but that are really helpfull are:
* summarizecolumns() - great for combining tables
* sumx() with the described filtering - this helps a lot to do the required calc
thanks again for your input, and happy new year!
Dear Jimmy,
maybe one more question; you worked with the creation of a new table in which you combined data from two other tables.
In power bi I can add this additional table, but can this be done in power pivot as well? (the creation of a new table only containing formulas?)
thanks!
- Hello
Don't understand fully your question.
Power pivot can be fed by power query. So you can query a data source, transforming it and hand over to power pivot, if this is what you meant.
All the best
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)
