Partner Segmentation Based on Products Sold

DAX DAX Calculations
1

Hi All - I’m trying to dynamically segment Partners based on the Products that they have sold in a given timeframe. I want to be able to select a month and calculate the unique PartnerIDs (and total sales) in these segments:

Sold Product A
Sold Product B
Sold Both

I’d then want to use these 3 segments in a slicer for some other type of analysis. I watched the customer segmentation video, which uses a supporting table to group the customers based on overall min/max sales. However, my criteria is based on selling certain products, not an overall range of sales. I’m unsure of how to structure my supporting table (or if this is even needed).

Any pointers would be greatly appreciated.

Sample PBIX and dataset attached.

Segment Test.xlsx (948.6 KB)
Segment Analysis.pbix (423.1 KB)

2

Hello all, bumping this post for more visibility.

3

Hi @acnyc88,

Thanks for supplying a PBIX and Data source file :+1:
I’ve added a FileLocation parameter, you can select your location from the list to restore all queries.

The data source contained a NULL row in the Dates table, preventing validation by “mark as date table” - so I removed that.

image
image860×362 24.6 KB

Created a New Table to include a “Both” row

image

Added that to the model

image

And created this measure for the count:

No of PartnerIDs = 
VAR _SelVal = SELECTEDVALUE( 'Prod Type'[Type] )
VAR _TableA = CALCULATETABLE( VALUES( Transactions[PartnerID] ), 'Prod Type'[Type] = "A", REMOVEFILTERS( 'Prod Type' ))
VAR _TableB = CALCULATETABLE( VALUES( Transactions[PartnerID] ), 'Prod Type'[Type] = "B", REMOVEFILTERS( 'Prod Type' ))
VAR _Both = COUNTROWS( INTERSECT( _TableA, _TableB ))
RETURN

SWITCH( _SelVal,
    "A", COUNTROWS( _TableA ) - _Both,
    "B", COUNTROWS( _TableB ) - _Both,
    "Both", _Both,
    COUNTROWS( VALUES( Transactions[PartnerID] ))
)

And this one for the Total Sales:

TotalSales PartnerIDs = 
VAR _SelVal = SELECTEDVALUE( 'Prod Type'[Type] )
VAR _TableA = CALCULATETABLE( VALUES( Transactions[PartnerID] ), 'Prod Type'[Type] = "A", REMOVEFILTERS( 'Prod Type' ))
VAR _TableB = CALCULATETABLE( VALUES( Transactions[PartnerID] ), 'Prod Type'[Type] = "B", REMOVEFILTERS( 'Prod Type' ))
RETURN

SWITCH( _SelVal,
    "A", SUMX( ADDCOLUMNS( EXCEPT( _TableA, _TableB ), "Sales", [Sales] ), [Sales] ),
    "B", SUMX( ADDCOLUMNS( EXCEPT( _TableB, _TableA ), "Sales", [Sales] ), [Sales] ),
    "Both", CALCULATE( [Sales], INTERSECT( _TableA, _TableB ), ALL( 'Prod Type' )),
    CALCULATE( [Sales], ALLSELECTED( Transactions ))
)

With this result.

image
image802×271 12.6 KB

Note. If required you can ‘hide’ the Group column by disabeling word wrap and minimizing the column width, so it’s no longer visible.

.
Here’s your sample file
Segment Analysis.pbix (343.9 KB)

I hope this is helplful.

3 Likes
4

Hello @acnyc88 , good to see that you are having progress with your inquiry. Did the response from @Melissa help you solve your inquiry? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

5

Helpful would be an understatement, this is great! Sorry about the date table, not sure how that happened.

Looking forward to trying this out with my actual dataset. Will come back to this thread if I run into any issues applying it.

Thanks!

6

Hi @Melissa ,

An additional ask came up on this topic, and I’m not quite sure how to tackle it. Instead of just counting the number of (or sales from) each Type of Partner, the ask is to be able to use the Type as a filter so users can see the corresponding PartnerIDs and sales in the selected timeframe.

If you move Type into a slicer and select “Both”, the table on the left would return blank. If you were to select “A” in the slicer, it would return all sales of Product A (including those from Partners that are Type = Both). I’d want the slicer selection to show only Partners of that type in the selected timeframe.
How do I solve for this? Is it another supporting table? Do I need to adjust the DAX? Any help would be greatly appreciated.

FYI - PBIX is same as one in the Solution.

Thanks!

7

Hi @AntrikshSharma - any ideas on being able to using the “Type” as a slicer?

Thanks!

8

Hi @acnyc88,

Please don’t ask new questions in solved topics, instead create a new one and provide all relevant information and data.

Thank you.