Count where Rank=1

[Anonymous]

Hi,

I'm looking for a way to make the following analysis work:

There is a slicer with Product selection. When specific products are selected, I need one measure (#customers) to show the number of customers selling both products.

Also, I need another measure (CountRank1) to show in how many customers Product A sells better, and in how many Product B sells better.

Please find the screenshot with explanation below.

The desired result is (also, for the measures to work on other visuals, not only the table)

	Product A	Product B
VOL	510	536
#customers	4	4
CountRank1	2	2

Please find the pbix sample file in the link below.

https://gofile.io/d/0MQJ51

I would really appreciate any help - spent many hours on this and couldn't make it work.

[CNENFRNL · Accepted solution]

Hi, @Anonymous , you might want to try new measures I authored again. (I attached a new file for it)

[Anonymous]

@CNENFRNL,

That was it - I could make the CountRank1 work by recreating your '# Customers overlap' logic and inserting condition in there:

IF( [Product rank] = 1, [Product overlap] +0, 0 )

Thanks! Appreciate your help a lot.

[CNENFRNL]

Hi, @Anonymous , I've so far only worked out how to count customers who sell all selected products; you might want to try a measure like this,

# Customers = 
COUNTROWS (
    FILTER (
        DISTINCT ( Sales[Customer ID] ),
        COUNTROWS (
            CALCULATETABLE (
                EXCEPT ( DISTINCT ( Products[Product ID] ), DISTINCT ( Sales[Product ID] ) )
            )
        ) = 0
    )
)

Names = 
VAR __ids =
    FILTER (
        DISTINCT ( Sales[Customer ID] ),
        COUNTROWS (
            CALCULATETABLE (
                EXCEPT ( DISTINCT ( Products[Product ID] ), DISTINCT ( Sales[Product ID] ) )
            )
        ) = 0
    )
VAR __names =
    CALCULATETABLE (
        DISTINCT ( Customers[Customer name] ),
        CALCULATETABLE ( Sales, __ids )
    )
RETURN
    CONCATENATEX ( __names, Customers[Customer name], UNICHAR(10) )

As to the rank part, it appears harder than expected. I'm also looking forward to solutions by others gurus.

[Anonymous]

Hi, @CNENFRNL 

Thanks for a different view at the customers count. I tried to play with your suggestion, but couldn't make the Ranking work.

# Customers only works as a separate visual - if we add it to the table which includes Products (on columns for example) - it doesn't show the desired result (it shows 7 instead of 4). That could be the key to solving the Ranking problem I believe, but I couldn't make it work.

[amitchandak]

@Anonymous , in rank you are using 'Prod_selection_1v1'[Product name], same should be used on visuals.

In that case rank 1 count is coming 5,5 what seems right as there are 10 customers.

[Anonymous]

Hi, @amitchandak ,

The issue here is - I only need to compare customers which have both products in their assortment. In others words, if at least one Product's VOL = 0, then dismiss this customer.