Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Count where Rank=1

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 AProduct B
VOL510536
#customers44
CountRank122

 

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.

6 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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.

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion
    5 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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.