Top 10 RANKX with two columns and IF ?

[RasmusN532]

Hi all,

Fairly new to power BI and have stumbled upon a issue I cant seem to find any solution on.

So firstly, I have a dataset example as follows:

Customer Hierarchy 1	Customer Hierarchy 2	Net Sales
Customer 1	Customer X	100,00
Customer 1	Customer Y	120,00
Customer 1	Customer ZA	130,00
Customer 3	Customer P	100,00
Customer 3	Customer A	50,00
	Customer B	300,00
	Customer C	200,00
	Customer F	80,00

Now what I want to do is to show a rankx as follows, and those who doesnt have rank 1-10 should be "Others and no rank"

Rank - Customer -

1 - Customer 1

2 - Customer 2

3 - Customer B

4 - Customer C

....

10 - Customer A

     - Other 

The issue here is that I´ve managed to create a measure which gives the rank to top 10, but only with hierarchy1..

IF there is a value in hierarchy 1 summarize (count as 1 customer) but if its blank its a unique customer in hierarchy 2.

For this data hierarchy 2 is used when there are bigger customers with several Sub companies that is treated as unique customers. 

But that doesnt mean that a customer from hierarchy 2 with blank value in hierarchy 1 cant have a higher rank.

The measure that I have now:

=IF(

    ISINSCOPE(CustomerHierarchy1),

    RANKX(

    ALL([CustomerHierarchy1]),

CALCULATE([Netsales],

[CustomerHierarchy1]   <> BLANK())

    ))

TLDR:

TOP 10 customers - if hierarchy 1 exist summarize, if its blank then summarize on hierarchy 2. show only top 10 customers and rest as a no rank category "others" 

Hopefully I´ve managed to explain, but please let me know if you need me to explain further.

I dont know if it matters but net sales are in a different table ( with relationship to this customertable etc) So netsales is a measure in above example 🙂

Thank you 

[v-kongfanf-msft · Accepted solution]

Hi @RasmusN532 ,

According to your description, I modified the formula. the Top10Customers parameter returns the table that satisfies the top10 condition, and the results are based on this table in the later conditional judgment.

Top10Customers1 = 
    TOPN (
        10,
        SUMMARIZE (
            'YourTable',
            'YourTable'[Customer Hierarchy 1],
            "TotalSales", CALCULATE ( MAX(YourTable[Net Sales]) )
        ),
        [TotalSales], DESC
    )

You can refine this formula and apply it to the complete data model.

Rank Customer = 
VAR Top10Customers =
    TOPN (
        10,
        SUMMARIZE (
            'YourTable',
            'YourTable'[Customer Hierarchy 1],
            "TotalSales", CALCULATE ( MAX(YourTable[Net Sales]) )
        ),
        [TotalSales], DESC
    )
RETURN
    IF (
        ISINSCOPE ( 'YourTable'[Customer Hierarchy 1] ),
        RANKX ( Top10Customers, [TotalSales] ),
        IF (
            ISINSCOPE ( 'YourTable'[Customer Hierarchy 2] ),
            RANKX (
                FILTER (
                    Top10Customers,
                    'YourTable'[Customer Hierarchy 1] = BLANK ()
                ),
                [TotalSales]
            )
        )
    )

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RasmusN532]

Thank you for this!