Ranking with exclusive products

Anonymous — Tue, 28 Feb 2023 12:34:59 GMT

In a report for a customer, I need to show the ranking of products compared to other customers. I can get the formula easy enough, but if there's exclusive products for the selected customers, Power BI does a thing where the rank output is equal to the number of games on other customers + 1. Easy enough work around if I could hardcode it, but this number will be different for each customer and will change monthly.

I had the idea to count the number of games on other operators with sales > 0, and adding an if function for when the ranking goes above this count, but now I'm struggling to find a distinct count with a filter for >0 sales and NOT being the selected operator.

I've no clue where to go from here and any help is appreciated. I've attached screenshots with example data, expected result, and given result. In the example, "Others" <> Bob.

Current Measure

BP Rank = 
VAR Others =  
    FILTER(
    ALL('Data table'[Customer]),'Data table'[Customer] <> SELECTEDVALUE('Lookup Customers'[ Customer])
    )
 VAR ProductCount = 
CALCULATE(
    DISTINCTCOUNT('Data table'[Game]),
    CALCULATETABLE(
        VALUES('Data table'[Game]),SUM('Data table'[Sale])>0
    ),
    CALCULATETABLE(
        VALUES('Data table'[Game]),
        NOT('Lookup Customers'[ Customer])
    )
)
VAR Ranking = 
RANKX(
    ALL('Lookup Product'[Product Name]),
    CALCULATE(SUM('Data table'[Sale]),
    Others
    )
)
RETURN
IF(Ranking > ProductCount, "N/A", Ranking)

topic Ranking with exclusive products in DAX Commands and Tips

Ranking with exclusive products