RANKX based on row total

[Anonymous]

Hi all,

I am curious if you can help me with the following, cause i got stuck:

I want to show the top 60 customers on three different pages based on sales spread over the three months from the quarter.

Page 1 = Top 1 - 20 customers;
Page 2 = Top 21 - 40 customers;
Page 3 = Top 41 - 60 customers.

I used the following formulas for ranking the customers:
"Ranking customers = RANKX(ALL(Customers_Odata), [Revenue invoiced], , DESC)"

For the calculation on page 1, I used the following formula:
"Ranking customers top 20 = IF([Ranking project task] <=20, [Revenue invoiced], BLANK())"

For the calculation on page 2, I used the following formula:
"Ranking customers top 21-40 = IF(AND([Ranking project task] >20, [Ranking project task] <= 40), [Revenue invoiced], BLANK())"

For the calculation on page 3, I used the following formula:
"Ranking customers top 41-60 = IF(AND([Ranking project task] >40, [Ranking project task] <= 60), [Revenue invoiced], BLANK())"

However, not 20 but 22 customers are displayed per page because the calculation is performed on each month and not on the Total.

It looks like this:

Is there any possibility to make just a calculation which is only based on the total column?

Kind regards,

Ramon

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Please kindly have a look at the link down below.

I created two measures and replaced one of them with your measure in the matrix visualization.

Each measure's subject includes my name, Jihwan, so you can easily find those.

The top 20 is based on the three-month-total.

Please check and please let me know if you need more.

Thank you.

https://www.dropbox.com/s/zph4xk4hdn4q4fn/Voorbeeld%20RankX.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.

[Jihwan_Kim]

Hi, @Anonymous 

Please correct me if I wrongly understand your question.

inside the ranking measure, instead of using [Revenue invoiced], try to use something like, 

   calculate ( sum ([Revenue invoiced]), allselected(datetable)) -> if you have year slicer  separately,

This helps to rank based on the one year total amount.

But you will show on the table visual -> [Revenue invoiced].

Or, if it is OK with you, please share your sample pbix file, then I can look into it to come up with more accurate measure.

Thank you.

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.

[Anonymous]

Hi @Jihwan_Kim ,

Thanks for the suggestion. However, I can't get it to work.
Sure I can attach the file for you. Super thoughtful of you.

Kind regards,
Ramon

[Anonymous]

Hi @Jihwan_Kim , 

Sounds kind of stupid, but how can I attach a PowerBI doc?

[Jihwan_Kim]

Hi, @Anonymous 

I think you can share the link.

[Anonymous]

Is this what you mean?

[Jihwan_Kim]

Hi, @Anonymous 

Sorry for the confusion.

What I meant was, 

- Save your pbix file in the google share drive( or One drive).

- and share the link of the file with me.

Thank you.

[Anonymous]

I guess this is what you are looking for.. 
https://1drv.ms/u/s!An16tA2yF-jjhSFXxL3mT7nVClrm?e=RFoYyz

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Please kindly have a look at the link down below.

I created two measures and replaced one of them with your measure in the matrix visualization.

Each measure's subject includes my name, Jihwan, so you can easily find those.

The top 20 is based on the three-month-total.

Please check and please let me know if you need more.

Thank you.

https://www.dropbox.com/s/zph4xk4hdn4q4fn/Voorbeeld%20RankX.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.

[Anonymous]

Hi @Jihwan_Kim ,

Is there also a opportunity to show only the subtotals of the 20 customers instead of all customers?

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Please try to replace the measure with the below one.

Ranking customers top 20 Jihwan V2 =
VAR top20table =
SUMX (
VALUES ( Customers[Customer Name] ),
IF ( [Ranking customers Jihwan] <= 20, [Revenue invoiced], BLANK () )
)
RETURN
IF (
HASONEVALUE ( Customers[Customer Name] ),
IF ( [Ranking customers Jihwan] <= 20, [Revenue invoiced], BLANK () ),
top20table
)

[Anonymous]

Thanks for your help and patience. This is exactly what I was looking for!

Kind regards,

Ramon