Filter for Customers that have Product A and Product B rather than Product A or Product B

[Anonymous]

Hi,

I currently have a clustered bar chart that shows a list of Products and the Percentage of customers that have them.

so Product A, Product B, Product C, etc.

Then I have another clustered bar chart that shows the percentage of customers in a region that have those products.

The intention of this is to be able to click on the various products in the Products Chart to see  what percentage of customers have these products in the region chart.

Currently, if you select two products, it shows the percentage of customers that have Product A OR Product B.  I need it to be able to calculate members that have Product A AND Product B.

The data set is set up as follows:

Product A       Member 1       Region 1

Product B       Member 2        Region 1

Product A       Member 3        Region 1

Product B       Member 3        Region 1

Product A       Member 4        Region 2

Product B      Member 5         Region 2

The goal is  if I selected  Product A - 50%, Product  B - 50%, Product A & B - 33% in Region 1

 Product A - 50%, Product B - 50% in Region 2

The Penetration Metric that currently calculates for OR is set up as follows:

Divide(

      DistinctCount(MemberNumber),

          Calculate(

                DistinctCount(membernumber,

                      AllExcept(Region)

 )))

Thanks in advance for any help or advice!

[v-frfei-msft]

Hi @Anonymous ,

1. Create a calculated table and add the column of it as a slicer.

Table = VALUES(Table1[Product])

2. To create two measures as below to get the excepted result we need.

Measure =
VAR _sele =
    CONCATENATEX ( 'Table', 'Table'[Product], "" )
VAR cal =
    CALCULATE (
        CONCATENATEX ( 'Table1', Table1[Product], "" ),
        VALUES ( Table1[Member] )
    )
VAR k =
    FILTER ( Table1, _sele = cal )
VAR sele =
    SELECTEDVALUE ( 'Table'[Product] )
VAR dou =
    CALCULATE ( DISTINCTCOUNT ( Table1[Member] ), KEEPFILTERS ( k ) )
        / CALCULATE (
            DISTINCTCOUNT ( Table1[Member] ),
            ALLEXCEPT ( Table1, Table1[Region] )
        )
VAR single =
    CALCULATE (
        DISTINCTCOUNT ( Table1[Product] ),
        FILTER ( Table1, Table1[Product] = sele )
    )
        / CALCULATE (
            DISTINCTCOUNT ( Table1[Product] ),
            ALLEXCEPT ( Table1, Table1[Region] )
        )
RETURN
    IF (
        ISFILTERED ( 'Table'[Product] )
            && DISTINCTCOUNT ( 'Table'[Product] ) > 1,
        dou,
        single
    )

Measure 2 = MAXX(VALUES(Table1[Member]),[Measure])

Regards,

Frank

[Anonymous]

Hi Frank,

Thanks so much for your help. I have one small edit to your code and then a couple of extra items I'm hoping you could help me with.

First, for the code, I believe the counts for VAR single should be on Member rather than Product.

Second, I don't believe the 2nd measure is functioning properly. If we had 2 out of 6  members that had product A and B in Region 1, the first measure would calculate 33% for each of them. Then, the second measure would  take the max which would be 33%

Third, is there any way so that when we first see the visual, it would show a calculation of members per region that have any product at all?  So  most (or in this dummy set - all) of the percentages would be 100%. Then when you selected specific products, it would filter down from 100%?

Thank you so much for your help. This was some very clever logic.

Hoping we can get this across the finish line 🙂

[v-frfei-msft]

Hi @Anonymous ,

To update the measure as below.

new measure = 
VAR _sele =
    CONCATENATEX ( 'Table', 'Table'[Product], "" )
VAR cal =
    CALCULATE (
        CONCATENATEX ( 'Table1', Table1[Product], "" ),
        VALUES ( Table1[Member] )
    )
VAR k =
    FILTER ( Table1, _sele = cal )
VAR sele =
    SELECTEDVALUE ( 'Table'[Product] )
VAR dou =
    CALCULATE ( DISTINCTCOUNT ( Table1[Member] ), KEEPFILTERS ( k ) )
        / CALCULATE (
            DISTINCTCOUNT ( Table1[Member] ),
            ALLEXCEPT ( Table1, Table1[Region] )
        )
VAR single =
    CALCULATE (
        DISTINCTCOUNT ( Table1[Member] ),
        FILTER ( ALL ( Table1 ), Table1[Product] = sele ),
        VALUES ( Table1[Region] )
    )
        / CALCULATE ( COUNT ( Table1[Member] ), ALLEXCEPT ( Table1, Table1[Region] ) )
RETURN
    IF (
        ISFILTERED ( 'Table'[Product] )
            && DISTINCTCOUNT ( 'Table'[Product] ) > 1,
        dou,
        IF ( ISFILTERED ( 'Table'[Product] ) = FALSE (), 1, single )
    )

[Anonymous]

Thank you Frank, this is working great when I select only 1 product  (after I updated VAR single to distinct count)  but I'm still running into issues when selecting more than 1 product (after I updated VAR single to distinct count.

Selecting Product A and B from the below returns 25%

Product Member Region

Product A	Member1	Region1
Product B	Member2	Region1
Product B	Member3	Region1
Product A	Member3	Region1
Product A	Member4	Region2
Product B	Member5	Region2
Product C	Member1	Region1
Product A	Member2	Region1
Product D	Member6	Region1
Product C	Member4	Region2

Thanks again for all your help!

[Anonymous]

Hi Frank,

I've done some additional research on the solution you've provided.

The data isn't rolling up at the total member level like it is for the single product selection. I tried implementing some of the same logic you used for the single but wasn't successful.

The below section of the formula always counts the distinct member count as 1:

VAR dou =

    CALCULATE ( DISTINCTCOUNT ( Table1[Member] ) ,KEEPFILTERS(k),Values(Table1[Region]) )

Thanks again in advance and for all your hard work on this.