Forum Discussion
Non Exclusive segment analytics
Hello
I have a dataset that now looks like this
| CustomerID | ProductID | SegmentID | CountOfSegments | PurchaseCategory |
| 000AA | Alpha | 1 | 2 | Non Exclusive |
| 000BB | Omega | 2 | 1 | Exclusive |
| 000CC | Beta | 1 | 2 | Non Exclusive |
| 000AA | Delta | 2 | 2 | Non Exclusive |
| 000CC | Epsilon | 3 | 2 | Non Exclusive |
| 000DD | Kappa | 4 | 1 | Exclusive |
Segments are essentially a group of products that belong to the same category. Segment 1 maybe Dairy, while product Alpha and Beta maybe Cheese and Milk.
The Count of Segment ID column calculates how many product categories that customer purchased. Since customer 000AA purchased product ID Alpha (which is in segment 1) and product ID Delta (which is in segment 2) they have 2 recorded product category purchases.
Purchase Category column is meant to inform on whether that customer's Segment ID purchase is Exclusive (meaning they did not make any purchases in any other category) or Non-Exclusive (meaning they made purchases elsewhere in our table of products in other segments).
My question is, how do I show (whether through a column or view) all of the products purchased by Non-Exclusive customers who purchased a Segment 1 product? In the data above customers 000AA and 000CC both purchased segment 1 products AND another product so I'm looking for a view that will return 50% Product ID Delta and 50% Product ID Epsilon.
I am very new to Power BI and this forum so please assume that I don't know prior DAX functions or the Power BI lingo. So I would appreciate your support very much if you can offer a step-by-step explaination and how to execute it. Thank you all so much
Good Morning Anonymous ,
First a bar chart needs to have a value on the y-axis. You cannot have a text field in both the y and x axis. Hence I think a stacked bar chart or clustered column chart will work for you.
Add a Customer Slicer and Segment Slicer to your page and I believe this will give you the visuals you are looking for.
Have attached a sample report below. Take some time to review it to see if it gives you what you need.
Edit: Read your other post. I don't think you need to create another table. In the attached file, I have created another Matrix visualization which appears to me to be what you are looking for.
Good Luck and regards,
4 Replies
Anonymous ,
Me again. Not quite exactly clear if this is what you want:
ProductPercent = DIVIDE(1, [CountofSegments], 0 )This site provides a comprehensive list of DAX Functions:
https://docs.microsoft.com/en-us/dax/
Results in this:
CustomerIDProductIDSegmentIDCountofSegmentsCustomerCategoryProductPercent
000AA Alpha 1 2 Non Exclusive 50% 000BB Omega 2 1 Exclusive 100% 000CC Beta 1 2 Non Exclusive 50% 000AA Delta 2 2 Non Exclusive 50% 000CC Epsilon 3 2 Non Exclusive 50% 000DD Kappa 4 1 Exclusive 100% If this does not meet your requirements, please try to clarify or show an example of the exact results you need.
Hey rsbin thank you so much for your help on this.
I am trying to develop a model that will tell me which products were purchased by non-exclusive segment members.If I create a view to further breakdown or slice Segment 1 customers (000AA and 000CC) - I want to see which products they purchased in other segments (Above and Beyond Segment 1 Purchases) so if I have a bar chart I'm imaging the x-axis to be ProductID and y-axis to be unique CustomerID and the bar values would show ProductID Delta = 1 and ProductID Epsilon = 1
From that bar chart or pie chart or whatever visual, i want to create a calcuation that says ProductID Delta = 50% of all other purchases made by customers who made a segment 1 purchase. And ProductID Epsilon = 50% of all other purchases made by customers who made a segment 1 purchase and so on.
I hope this helps to clarify it
Good Morning Anonymous ,
First a bar chart needs to have a value on the y-axis. You cannot have a text field in both the y and x axis. Hence I think a stacked bar chart or clustered column chart will work for you.
Add a Customer Slicer and Segment Slicer to your page and I believe this will give you the visuals you are looking for.
Have attached a sample report below. Take some time to review it to see if it gives you what you need.
Edit: Read your other post. I don't think you need to create another table. In the attached file, I have created another Matrix visualization which appears to me to be what you are looking for.
Good Luck and regards,