How to relate KPI targets against Sales Value

[Anonymous]

I am trying to compare the sales value against the target value accross time and regions, the issue i am having is that i can see the sales per region and date, and separately i can see the target per date (lookup so this is related) but not by region, after unpivoting the Target Table when i try to make a relationship betwen the table Target and Region i get an error because the Region table doesnt contain unique values; I created a region KEY table and made a relationship to the region table but still no luck, when i try to compare the Target with Sales, powerbi sum all the target values in the regions example:

 

I have 2 lookup tables: A data table and Regions table:

CODE	COUNTRY	REGION
AT	Austria	DACH
BG	Bulgaria	Eastern Europe
PL	Poland	Eastern Europe
CZ	Czech Republic	Eastern Europe

then one Sales Data table:

Shipping Country	Products Item	Products Quantity	Products Price	Order Total	Shipping Date
DE	2447AB	1	33.6	33.6	2019-11-22
DE	7626AB	2	25	50	2019-11-28
PL	1370AB	20	18	360	2019-11-20
DE	2601AB	1	0	0	2019-11-19
DE	314AB	30	15	450	2019-11-18
PL	3154AB	10	13.5	135	2019-11-20

And the Target Table:

Month	DACH	Eastern Europe
Oct-19	377,033	92,125
Nov-19	404,556	58,246
Dec-19	222,883	259,211

Relationship View:

I would really appreciatte any help on this.

Thanks 

[FirasAlramli]

I advise you to use Star schema relationship.

modify your tables by merge (Territory , Region,Target) and make it as one table.

[amitchandak]

Your relationship view seems fine.  Both relations with Date and Region is One to Many with Target.

If possible please share a sample pbix file after removing sensitive information.
Thanks

[Anonymous]

Hello @amitchandak Thanks for the reply.

You can download the pbix from onedrive here: https://1drv.ms/u/s!AguM2qARs0_yhlk819ZM_o5SBCQ6?e=OY4suB

Thanks in advace for your help.

[FirasAlramli]

after see the file - the problem is

Sales by Territory code  and Target by Region code.

Solution:

Modify Target table to be by Territory - 

merge territory code , region and target togther. 

[amitchandak · Accepted solution]

You can take Region from Region Table, not from Territory.