How to connect a table in the datamodel?

[Jason2500]

Hi

I am a beginner in Power BI. This question might be very silly for the community, just to understand and for my learning purpose

I have a dataset as below

Customer Name	Product	Order value	Year
AAA	Prod1	100	2020
ABC	Prod2	150	2021
ABD	Prod1	200	2018
AAA	Prod3	250	2017
ABC	Prod4	300	2018
ABD	Prod1	15000	2015
AAE	Prod3	20030	2016
ABC	Prod2	100	2019
ABE	prod4	120	2017

I wanted to visualize the order value based on the product group.

I want to group 1 - the sum of product 1 & Product 2; group 2 - the sum of product 3 and product 4

What I did?

Used Power query for the below

1) First I created a unique year column separately
2) I filtered out the product 1 and product 2 and did a year group by based on the sum of contract -> stored in a separate table called a group 1 table
3) Again I filtered out the product 3 and product 4 and did a year group by based on the sum of contract -> stored in a separate table called a group 2 table

Now I have three tables such as year, group 1, group 2 table. I connected year with both group 1 and group 2 table in the data model.

My questions

1) Is this the right approach?
2) I want to visualize the order values order value based on product group by year - showing the trend over the years (Thinking of line graph??)

I did the line graph but it's not coming properly (just getting the two vertical lines)

3) I want to have the difference between both the product groups in %, how to calculate in Dax as well what graph should I use to show the trends with differences?

Please advise. Sorry if my question is silly. Thanks, heaps for the community, very informative and supportive.

[amitchandak · Accepted solution]

@Jason2500 , You can create a new column and use that to analyze

Group =Switch( True(),
[Product] in {"Prod1","Prod2"}, "Group1",
[Product] in {"Prod3","Prod4"}, "Group2",
"Other"
)

It is better to have separate table for customer , product(product, group) and year.

Star Schema : https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/