Customer Goals- Help!

[Jmccoy]

Alright let me paint a picture so you know what we are working with. I have a star schema that tracks high level details about a quote.  The fact table includes quote number, freight, Total sales, and all of my dimension keys.

The dimensions I am focusing on and needing help with are the Customer Dimension and Market Segment Dimension. 

My sales manager provided me with a excel file provides me with a Customerid  and goals broken out by Market Segment. I do not currently house these goals in any tables.

  Ex. in snip it. 

Customer ID in the spreadsheet lines up with the business key in the Customer Dimension. Each of the Segments match with the Market Segment Dimension. 

What are some typical ways to handle this? I was thinking about making a table in SQL Server with CustomerID, Unpivot the column headers, and tie it to my fact table. Just not exactly sure how I should make these relationships. Should I also join Market Segment table to that new table and capture the SegmentID Then tie it to the fact table that way? 

[v-yangliu-msft]

Hi  @Jmccoy  ,

If you want to check the details of customer and market

Here are the steps you can follow：

1. Click transfrom data to enter power query

2. Select the columns [CustomerID] and [location], and click transform – unpivot columns -- unpivot other columns

3. Result.

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

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

[Jmccoy]

Thank you for your response. I actually took care of that part in SQL, the part that I am having a hard time with is building a relationship between this new table and my current star schema. I am not sure exactly where this would fit in to provide me with accurate numbers. This table will cover the goals for each customer, however, I need to provide the user with visuals that display actual vs the goals. 

[amitchandak]

@Jmccoy , Unpivot the table and bring the market segment to rows. Better to have separate dimension tables. That is as per need.

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

Creating dimension in power query

https://www.youtube.com/watch?v=kU2M1LmNvNo

https://www.youtube.com/watch?v=vHuhbvYCiNc

unpivot:https://www.youtube.com/watch?v=2HjkBtxSM0g