Data Model

[Prabha45]

Hi All,
I have a table like the below. I need to create a bar chart with Actuals and target. For 2023 I can use 2023 target. But I need to show Q4 2023 and Q1 2024 at a time and the target should  show 50 for Q4 2023 and 25 for Q1 2024.

Category	Sub category	Region	Quarter	Actual	Comments	Owner	Target 2023	Target 2024	Target 2025
Road	Bike	Global	Q4-2023	10	Working	David	50	25	30
Road	Bike	Asia	Q4-2023	5	N/A	David	0	0	0
Road	Bike	Uk	Q4-2023	5	Under validation	Smith	0	0	0
Road	Bike	Global	Q1-2024	20	N/A	Smith	0	0	0
Road	Bike	Asia	Q1-2024	15	N/A	Henry	0	0	0
Road	Bike	Uk	Q1-2024	5	Working	Henry	0	0	0
Electronics	Laptop	Global	Q4-2023	10	Manufacturing	Micheal	15	5	20
Electronics	Laptop	Asia	Q4-2023	5	Manufacturing	Micheal	0	0	0
Electronics	Laptop	Uk	Q4-2023	5	N/A	Neil	0	0	0
Electronics	Laptop	Global	Q1-2024	15	working	Neil	0	0	0
Electronics	Laptop	Asia	Q1-2024	10	N/A	Neil	0	0	0
Electronics	Laptop	UK	Q1-2024	5	Working	Neil	0	0	0

Thanks in advance.

[lbendlin · Accepted solution]

You mention "Data Model"  in the title.  That's exactly what you need.  Your data model needs to have at least three tables - Dates, Actuals, and Targets. Probably more (product hierarchy etc),  but with these three you will be able to accomplish your task.

[v-junyant-msft · Accepted solution]

Hi @Prabha45 ,

This part of your data model doesn't make sense and doesn't achieve your desired results under the current data model:

Please add a table like this:

Create relationship between two tables:

The final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin · Accepted solution]

You mention "Data Model"  in the title.  That's exactly what you need.  Your data model needs to have at least three tables - Dates, Actuals, and Targets. Probably more (product hierarchy etc),  but with these three you will be able to accomplish your task.