Forum Discussion

RasmusN532's avatar
RasmusN532
Helper I
2 years ago
Solved

Top 10 RANKX with two columns and IF ?

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 1Customer Hierarchy 2Net Sales
Customer 1Customer X100,00
Customer 1Customer Y120,00
Customer 1Customer ZA130,00
Customer 3Customer P100,00
Customer 3Customer A50,00
 Customer B300,00
 Customer C200,00
 Customer F80,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 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.