Rankx based on multiple columns

[Anonymous]

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!

[v-rzhou-msft]

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

[v-rzhou-msft · Accepted solution]

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.