Base v Segment Filtering and Visualations

[catfood49]

I have a requirement to do analysis of two sets of customers who meet different criteria, i.e.

segment 1 - customers who have bought products from a certain category

segment 2 - all customers who who never ordered

I am using Power Bi to connect to an Azure Analysis Services Tabular Model with live connection. Initially I tried creating Calculated Columns within my SSAS model to flag customers who met the cirtieria for Segment 1 and 2 but came across two problems; I was getting Circular Dependency Errors when trying to create the column, and have since learnt that the criteria for segments needs to be selected by the user in Power BI i.e. the category changed for segment 1 customers. The attached image shows the simplified data model (relationships aren't shown but hopefully are pretty self-explanatory):

So my two questions are:

1. How should I flag these customers, either in SSAS or Power BI (the complication for me being not being able to create measures within Power BI Desktop due to Direct Query)
2. Once I have the records flagged, is there a simple method for comparing the two sets of customers. With segment 1 likely to only contain a few thousand customers compared to the base which will contain millions, do I just have to make all my visualisations show % of segment (i.e. 40% of segment 1 are Male, 50% Female, 10% Unknown against 75% Male for segment 2 etc.) or is there is a better way to compare two sets containing different numbers of records?

I appreciate this question is quite wordy and both specific yet vaque at the same time! Thanks in advance, any help and guidance is really appreciated.

[v-jiascu-msft]

Hi @catfood49,

Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

Best Regards!
Dale

[catfood49]

as an additional question/observation... when looking for video tutorials on DAX they all seem to use Power BI within Import which allows for the creation of Measures within Power BI however due to the data limitations and my large data set, using the Import method method is not going to be possible. One of a selling point of Power BI is the ability to analyse large data sets however I have found the data limit a real issue.

[v-jiascu-msft]

Hi @catfood49,

If your Analysis Services is a Tabular mode, you still can create some measures. The answers of your questions are based on your mode.

1. This could be easy. Filter the category, the customer will be filtered too.

2. Try a formula like this please.

Measure =
IF ( COUNTROWS ( 'Internet Sales' ) = 0, "No Orders", "Ordered" )

Best Regards,

Dale

[catfood49]

Hi Dale

Firstly, thanks for the reply. I'm now back on to this after the Christmas break. I've been looking into the answer you gave but I doesn't really allow me to achieve what I need. 

When segmenting customers, the segments could be mutually exclusive of each other. For example, I could want to compare all customers who have ordered a products (or number of products) from a category and compare these customers against all customers aged between 20 and 30 who are from the UK.

My interpretation of your answer would mean that the whole customer dataset would be segmenting as to whether they met one particular criteria or not rather than Segment 1 customers, Segment 2 customers, and then anyone else who didn't meet the criteria for either segment. Does that make sense?

[v-jiascu-msft]

Hi @catfood49,

I created a demo here. Please check it out. You can add a slicer of age to compare age between 20 and 30. If you want more formulas, please post the conditions here.

HaveSales = COUNT('Customer'[Gender])

NoSales =
VAR haveSalesCustomers =
    DISTINCT ( 'Sale'[CustomerKey] )
RETURN
    CALCULATE (
        COUNTX (
            FILTER ( Customer, NOT Customer[CustomerKey] IN haveSalesCustomers ),
            [CustomerKey]
        ),
        ALL ( 'Product' )
    )

Best Regards,

Dale

[catfood49]

 

Hi Dale

Thanks again for replying and setting up the demo however I dont think this is doing what I need. The image included shows the dataset which can be used to represent the type of analysis I need to do.

I have a requirement from a business user that they wish to do customer analysis comparing any customers who have bought products within the 'childrens gloves' sub-category against customer who are aged 21-30 AND from the UK. The 'Segment Criteria' details this as part of the image and makes up my 2 segments.

Using the data model and example data, Segment A would contain customers C4, C5 and C8 as they all have sales for products within the Childrens Gloves sub-category. Segment B would contain customers C1, C2, C3 and C6 as these customers are all aged 21-30 and from the UK.

Using these two segments I then want to create the visualisations. An example would be to show the gender divide for the two segments (Segment A is 75% Male, 25% Female, Segment B is 33% Male, 33% Female and 33% Unknown). Another example would be comparing Opt-Ins of the two segments.

The additional complication is that the business user may want to change the critiera for either Segment. For example, they may then want to compare all customers who have bought from the 'Mens Shirts' sub-category and compare them against all customers from France.

I have created a power bi desktop file which contains the sample dataset in the image below if you could take a look?

 

I hope that makes sense. Once again, I really appreciate your help. I have been trying to achieve what I need from the demo you sent over but I can't make it do what I need.

Thanks

Pete

[v-jiascu-msft · Accepted solution]

Hi @catfood49,

Perhaps here is the solution. You can check it out in this file.

1. Create two new tables and more tables as you wish. You can find out why from the snapshot.

Gender =
FILTER ( VALUES ( Customer[Gender] ), ISBLANK ( [Gender] ) = FALSE () )

Opt-In =
FILTER ( VALUES ( Customer[Opt-In] ), ISBLANK ( [Opt-In] ) = FALSE () )

2. Two measures.

SegmentAInternalCompare =
VAR SegmentA =
    CALCULATETABLE (
        VALUES ( Sale[Customer Key] ),
        ALL ( Customer[Country], Customer[Age Band] )
    )
VAR allSegmentA =
    CALCULATETABLE ( VALUES ( Sale[Customer Key] ), ALL ( Customer ) )
RETURN
    DIVIDE (
        COUNTX ( SegmentA, [Customer Key] ),
        COUNTX ( allSegmentA, [Customer Key] ),
        0
    )

SegmentBInternalCompare =
VAR SegmentB =
    CALCULATETABLE ( VALUES ( Customer[Customer Key] ), ALL ( 'Product' ) )
VAR allSegmentB =
    CALCULATETABLE (
        VALUES ( Customer[Customer Key] ),
        ALLEXCEPT ( Customer, Customer[Country], Customer[Age Band] )
    )
RETURN
    DIVIDE (
        COUNTX ( SegmentB, [Customer Key] ),
        COUNTX ( allSegmentB, [Customer Key] ),
        0
    )

Best Regards,

Dale

[catfood49]

Hi @v-jiascu-msft

Thanks so much for this. I'll be honest, I don't fully understand how the measures work right but it does seem to do what I need. I'll spend more time playing around with the code and hopefully I'll understand it more.

Thanks again Dale.

Pete