cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jmccoy
Helper II
Helper II

Customer Goals- Help!

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. 

Screenshot 2021-02-19 073626.png

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? 

 

 

3 REPLIES 3
v-yangliu-msft
Community Support
Community Support

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

v-yangliu-msft_0-1614040554398.png

3. Result.

v-yangliu-msft_1-1614040554405.png

 

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.

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
Super User IV
Super User IV

@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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors