Calculate % of complition sales vs plan

[Oscar1]

Hello,

I would need some help to create a table that calculates me the % of complition of the sales per month vs the plan (if possible also per year).

I  have 2 data sets. The first is the plan of each CFR that works in 1 of the 2 shops per month and year:

Month	Year	Shop	CFR	Plan
1	2.018	Shop1	A	1
1	2.018	Shop1	B	2
1	2.018	Shop1	C	3
1	2.018	Shop2	D	4
1	2.018	Shop2	E	5
1	2.018	Shop2	F	6
2	2.018	Shop1	A	2
2	2.018	Shop1	B	3
2	2.018	Shop1	C	4
2	2.018	Shop2	D	5
2	2.018	Shop2	E	6
2	2.018	Shop2	F	7

The second are the sales per month per CFR:

Month	Year	CFR	Sales
1	2.018	A	1
1	2.018	B	2
1	2.018	C	3
1	2.018	D	4
1	2.018	E	5
1	2.018	F	6
2	2.018	A	2
2	2.018	B	3
2	2.018	C	4
2	2.018	D	5
2	2.018	E	6
2	2.018	F	7

I connect both of them by a key of year&month&CFR.

I would be interested in calculating the % of accomplishment per month (sales/plan) for each of the CFRs, but also for each of the shops and for all the group (the 2 shops together).

In case of the shops and the total group, the plan is adding up all the plan from the CFRs.

Thank you!

[Lind25 · Accepted solution]

Hi Oscar

You need to create dimension tables for Month, Year, CFR (note: shop is not included).

These can then be related to your Plan and Sales tables.

Measures will then be simple to write and work automatically at any level of detail.

Sales plan:= sum(PlanTable[Plan])

Sales actual:= sum(SalesTable[Sales])

Actual / plan:= divide([Sales actual],[Sales plan])

Use your dimension tables on your tables / charts rather than the Month / Year / CFR stored in the data tables.