Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Rankx based on multiple columns

Hi All,

 

So I have this problem using rankx. I am using Excel, btw.

 

I have to rank my sales ignoring filter atrributes (Segment, Country, Product, Discount Band).

    This was my measure expression: 

  • Financial Sales:=CALCULATE(SUM(financials[Sales]))
  • Sales Rank:=RANKX(ALL(financials[Segment], financials[Country], financials[Product], financials[Discount Band]), [Financial Sales],, DESC, Dense)

After ranking my sales, I wanted to show the top 10 sales for the whole table using the following expressions:

  • Top10 Sales:=if([Sales Rank]<=10, [Financial Sales], BLANK())
  • Top10 Sales Rank:=if([Sales Rank]<=10, [Sales Rank], BLANK())

 

So basically, the outcome was what I wanted (see figure A). However, some weird entries appeared when I enabled the grand totals for column (see figure B).

 

Figure A.

 

Figure B.

 

How do i fix this?

Here's the file should you need to look at it. It's just a financial sample data from microsoft.

 

Thanks in advance!

 

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous 

    I download your excel and update your measure in Power BI Desktop.

    Financial Sales = 
SUMX (
    FILTER (
        ALL ( financials ),
        financials[Segment] = MAX ( financials[Segment] )
            && financials[Country] = MAX ( financials[Country] )
            && financials[Product] = MAX ( financials[Product] )
            && financials[Discount Band] = MAX ( financials[Discount Band] )
    ),
    financials[ Sales]
)
    Rank = 
IF(ISINSCOPE(financials[Discount Band]),RANKX(ALL(financials),[Financial Sales],,DESC,Dense),BLANK())

    Build a Matrix visual to show the result.

    You can download the pbix file from this link: Rankx based on multiple columns

     

    Best Regards,

    Rico Zhou

     

    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
    5 years ago

    Hi Anonymous 

    I download your excel and update your measure in Power BI Desktop.

    Financial Sales = 
SUMX (
    FILTER (
        ALL ( financials ),
        financials[Segment] = MAX ( financials[Segment] )
            && financials[Country] = MAX ( financials[Country] )
            && financials[Product] = MAX ( financials[Product] )
            && financials[Discount Band] = MAX ( financials[Discount Band] )
    ),
    financials[ Sales]
)
    Rank = 
IF(ISINSCOPE(financials[Discount Band]),RANKX(ALL(financials),[Financial Sales],,DESC,Dense),BLANK())

    Build a Matrix visual to show the result.

    You can download the pbix file from this link: Rankx based on multiple columns

     

    Best Regards,

    Rico Zhou

     

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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous 

    Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your problem.

     

    Best Regards,

    Rico Zhou