Actual and Target Sales by Quarter

[YYlee]

This is my database

I wish I can get like this 

I link the "Location Name" and "Quarter" between excel 1 & excel 2. 
However, when I insert those data in BI, cluster column chart and box & whisker looks good, BUT, 
when I choose Excel 1 - Location Name, Quarter, Net Revenue + Excel 2 - Target Revenue, BI auto sum up the Target Revenue for me which I prefer them to be seperated by different location and quarter so I can compare each of them. 

Could I look for advice? 

[MattiaFratello]

Hi @YYlee, 

Can you send sample data please?

[YYlee]

Dear Mattia, Thanks. 
Please refer below: 

This is Target

Location Name	Q1	Q2	Q3	Q4
AAA	5000	8000	7000	6000
BBB	5000	9000	6500	7000
CCC	10000	9000	8600	15000

This is Actual Sales

Location Name	Invoice Date	Net Revenue
AAA	2/1/2025	$              100.00
AAA	10/2/2025	$                 15.00
BBB	3/1/2025	$                 25.00
BBB	6/1/2025	$                 55.00
BBB	25/1/2025	$              500.00
CCC	6/2/2025	$              155.00
CCC	3/3/2025	$                 85.00
CCC	10/3/2025	$                 63.00
AAA	5/4/2025	$                 78.00
AAA	3/5/2025	$                 95.00
BBB	1/3/2025	$                 46.00
BBB	4/4/2025	$                 85.00
BBB	26/5/2025	$                 12.00
CCC	1/5/2025	$                 95.00
CCC	9/5/2025	$                 27.00
CCC	28/4/2025	$                 14.00

[MattiaFratello · Accepted solution]

Hi @YYlee,

Please see if this could work

[YYlee]

Dear Mattia, 

I think this looks suitable. 
May I check if "Location Name" = AAA, BBB, CCC and "Quarter" = Q1,Q2,Q3,Q4?

[MattiaFratello]

Hi @YYlee,

I created them like this to suit your example but of cource they can be expanded/adjusted to your needs.

Let me know if you need more help with recreating such tables starting from your dataset.

It's always good to connect fact tables with dimension tables.

If you would like to read a bit more about it -> https://learn.microsoft.com/en-us/power-bi/guidance/star-schema

Please mark my answers as solutions if you feel like I solved your problem

Any Kudos is much appreciated!

[YYlee]

Thank you very much, it works perfectly!!