Problem with Ranking (RankX), Showing all Customers


#1

Hi Sam:

I hope you can help me with this problem :neutral_face:

I have a matrix with two levels of drill, Family comes first, Country_Name comes second. Thanks to the support of PowerBI Community I was able to find the solution to properly Rank when you are in Level 1, and when you go down to Level 2 (pretty cool by the way)…

The problem is because the use of ALL Function in RANK formula, you are obtaining all “Families” and all “Contry_Name”, instead of only those with sales.
With the current design, showing all families is not a big deal, but it is for customer_names, where a long list of countries without sales are present…

The only way I’ve found to make this work is adding a visual-filter on Visualization pane, removing all countries without sales, indicating “Total Sales” greater than 0.

Here the link to the conversation:

PowerBI Community

Here the link to the file.

Dummy File for Downloading

If it is a problem to re-direct to external web, let me know, to remove the link.

Thanks in advance for the support.:grinning:


#2

I think to fix this is not too difficult.

I just added this to the beginning of the formula

IF( ISBLANK( [Total Sales] ), BLANK(),…

Ranking = 
VAR IsCountryFiltered = ISFILTERED( Master_Customer_Suppliers[Country_Name] )
VAR IsFamilyFiltered = ISFILTERED( Family[Family] )

RETURN
IF( ISBLANK( [Total Sales] ), BLANK(),
IF( IsCountryFiltered && IsFamilyFiltered, 
	RANKX( ALL( Master_Customer_Suppliers[Country_Name] ), [Total Sales], , DESC, Skip ),
		IF( NOT( IsFamilyFiltered && IsCountryFiltered ),
			RANKX( ALL( Family[Family] ), [Total Sales], , DESC, Dense ),
			 BLANK())) )


#3

Just looking at your model.

Lot’s of improvements could be made here to make everything more intuitive

Review this course when you have time.


#4

Hi Sam:

Thanks for the answer.

About the points to improve desing performance, I quite agree, this is a dummy model, for running test in formulas and calcualtions. Filesize is not big, so we don’t experience performance problems of any kind.

With respect to your suggestion of including IF(ISBLANK), I have to say it doesn’t work, all countries are still appearing on the second level drill.

Do you have any other idea?

Regards,


#5

Hi Sam:

Problem solved.
Your proposal works, the point was that you have to add the condition "IF(ISBLANK) or IF(Total Sales <= 0, BLANK(), ____) to the rest of aggregations created.

If you don’t, aggregations like “Total Sales LY” or “Total Budget” add extra rows even when Total Sales is zero, which is correct under a Dax Perspective.

The good thing is that, because Subtotal and Grand Total are calculated independenly in DAX (not summing values above), the result you get as Grand Total is correct, in spite of not showing all values… This helps if you are looking for the right calculation in “%of Budget Accomplished” for instance.

This is fact is important to include somehow on the model to avoid confusions on users (they could sum up values with different subtotal-Grand Total result).

Sam, thanks for everything.:grinning:

Here the model with all the amends done :wink: hope it helps other users :blush:

Dummy Model

Regards,


#6

That’s great. Chrs