formulas between different tables

[mat007mat]

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

[Jimmy801]

Hello @mat007mat 

were you able to solve the problem with any reply given?

If so, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

All the best

Jimmy

[v-juanli-msft]

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.

[mat007mat]

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)

[Jimmy801 · Accepted solution]

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

link 

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

[mat007mat]

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!

[Jimmy801]

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

[mat007mat]

This 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!