topic Re: RANKX based on row total in DAX Commands and Tips

RANKX based on row total

Anonymous — Fri, 26 Mar 2021 12:37:12 GMT

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

Re: RANKX based on row total

Jihwan_Kim — Sat, 27 Mar 2021 16:14:16 GMT

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.

Re: RANKX based on row total

Anonymous — Mon, 29 Mar 2021 08:44:44 GMT

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

Re: RANKX based on row total

Anonymous — Mon, 29 Mar 2021 08:52:43 GMT

Hi @Jihwan_Kim ,

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

Re: RANKX based on row total

Jihwan_Kim — Mon, 29 Mar 2021 09:42:18 GMT

Hi, Anonymous

I think you can share the link.

Re: RANKX based on row total

Anonymous — Mon, 29 Mar 2021 10:01:31 GMT

Is this what you mean?

Re: RANKX based on row total

Jihwan_Kim — Mon, 29 Mar 2021 09:53:40 GMT

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.

Re: RANKX based on row total

Anonymous — Mon, 29 Mar 2021 10:45:08 GMT

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

Re: RANKX based on row total

Jihwan_Kim — Mon, 29 Mar 2021 11:29:12 GMT

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.

Re: RANKX based on row total

Anonymous — Mon, 29 Mar 2021 11:54:52 GMT

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

Kind regards,

Ramon

Re: RANKX based on row total

Anonymous — Mon, 29 Mar 2021 14:38:55 GMT

Hi Jihwan_Kim ,

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

Re: RANKX based on row total

Jihwan_Kim — Mon, 29 Mar 2021 15:35:09 GMT

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
)