Non Exclusive segment analytics

[Anonymous]

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

[rsbin]

@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.

[Anonymous]

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

[rsbin · Accepted solution]

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,

[rsbin]

@Anonymous ,

Gettin' late in the day here.  Lemme give this some further thought this evening and I should have time tomorrow to hopefully provide some suggestions.

Regards,