Power BI - Display Top 3 by Rank

[micky007]

Hi,

I've attached an example of my data and project of what im trying to do but for some reason i cant get it to work correctly. No doubt I'm probably doing this wrong so any help and advice would be much appreciated.

I'm trying to list the top 3 customers that have ordered the most (by revenue) to be displayed in a table but its not working the way I thought it would.

As you can see from the project I've created 2 calculated columns, one to calculate the total revenue from all the orders they've placed and another to rank the customers by most revenue using the below DAX:

Calculate total revenue from all orders by the same customer:

Please Login or Register  to view this content.

Rank Customer by most revenue from their orders:

Please Login or Register  to view this content.

When looking at the data the new columns are working as expected. But the issue i have is showing the data in a table, as you can see from the image below its showing rank 1, 2 and 4. How come its now showing the 3rd biggest customer and instead showing the 4th biggest?

powerbi-test.PNG

Also is there a simpler way to do what I'm after?

Any help and advice would be much appreciated and thank you.

[AliGW]

Baffling! I can't see any obvious mistakes in the .pbix file.

[micky007]

Thanks for taking a look into it Ali. I know right! I thought it would work fine but I have no idea why this is happening. Hopefully someone can see an issue and help me resolve the problem.

Thank you though

[Bo_Ry]

Don't use calculated columns for summarizing value.
Use Measure
Rev = sum('GB Product Orders'[Total Amount])

RankRev = RANKX(ALL('GB Product Orders'[Account: Account Name]),[Rev])

Then filter rank less or equal than 3

[AliGW]

Here's another way. I created a query to calculate grouped totals, and then used this for the visual.

[micky007]

Thank you very much both of you!

Two great examples which I've looked at both and fully understand what you've done.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[micky007]

Already done both of those haha