Ranking customers by sales, top seller shown as #2

[Matt78]

Hi

I have DAX string here where I want to simply rank customers by sales and have ranking #.

RANK = RANKX(ALL('CustomerTable'[BUSINESS_UNIT_NAME_FI]),[Total Sales],,DESC)

This works fine if I have use a matrix where all customers are listed BUT if I filter this to show only one customer and would like to see what is the rank, it shows the highest selling customer as #2 and second best selling as #3. I suppose it thinks #1 is total sales..?

How should I fix my DAX so that when I filter customers one by one, it gives the correct rank?

BR

Matt

[Anonymous]

Hi  @Matt78 ,

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Liu Yang

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

[Anonymous · Accepted solution]

Thanks for the reply from @rajendraongole1 , please allow me to provide another insight:

Hi  @Matt78 ,

I created some data:

Here are the steps you can follow：

1. Create measure.

True_Measure =
var _table1=
SUMMARIZE(
    ALL('CustomerTable'),[Customer],"Total Sales Measure",[Total Sales])
var _table2=
ADDCOLUMNS(
    _table1,"Rank",RANKX(_table1,[Total Sales Measure],,DESC))
return
MAXX(
    FILTER(_table2,[Customer]=MAX('CustomerTable'[Customer])),[Rank])

2. Result:

Before filtering:

After filtering:

Best Regards,

Liu Yang

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

[Matt78]

Hi

With this I get the following result:

br

Matt

[rajendraongole1]

Hi @Matt78 - I have done slight modification to your measure can you try the below measure, allselected ensures ranking considers the current filters except for the context of the current row.

RANK = RANKX(
ALLSELECTED('CustomerTable'[BUSINESS_UNIT_NAME_FI]),
[Total Sales],
,
DESC,
DENSE
)

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[Matt78]

Hi,

When I have all customers listed it ranks ok but still if I filter to only one customer, it gives #1 rank to every customer

br

Matt

[rajendraongole1]

Hi @Matt78 - Can you try the below refined measure

Customer Rank =
IF(
ISFILTERED('Customers'[CustomerID]),
RANKX(
ALL('Customers'[CustomerID]),
CALCULATE(SUM('Sales'[Amount]))
),
RANKX(
ALLSELECTED('Customers'[CustomerID]),
CALCULATE(SUM('Sales'[Amount]))
)
)

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!