Problem with Ranking (RankX), Showing all Customers

[Ciria]

Hi All:

I hope you can help me with this problem

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.

[sam.mckay]

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())) )

[sam.mckay]

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.

Data Transformations & Modeling

[Ciria]

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,

[Ciria]

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.

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

Dummy Model

Regards,

[sam.mckay]

That’s great. Chrs

[dkettinger]

What if in addition to Blank values for Total Sales, the customer has 0 values? Can you force 0s to blank using a Switch statement or nested IFs, so that you don’t have the first N number of customers with 0 Total Sales showing as the first N customers in your ranking?

DAX Used:

Customer Rank =
IF(ISBLANK([Total Sales]),BLANK(),
    RANKX(
        FILTER(ALL(Customer[Customer Name]), NOT(ISBLANK([Total Sales]))),
            [Total Sales], , ASC) )

Customer Rank zeros = 
IF([Total Sales] <= 0, BLANK(),
    RANKX(
        FILTER(ALL(Customer[Customer Name]), NOT(ISBLANK([Total Sales]))),
            [Total Sales], , ASC) )

Custom Rank Blank and Zeros = 
IF(ISBLANK([Total Sales]) || [Total Sales] <= 0,BLANK(),
    RANKX(
        FILTER(ALL(Customer[Customer Name]), NOT(ISBLANK([Total Sales]))),
            [Total Sales], , ASC) )

[sam.mckay]

Yes you can.

What I would do here though, is I would complete the ISBLANK() logic all the way at the beginning within the Total Sales measure.

That way it will just filter through to any other calculations you complete from there on out. This is why ‘measure branching’ is such a good technique, because you only should have to solve this once at the core measure which is Total Sales in this case.

[dkettinger]

Sales Analytics - v3.pbix (2.6 MB)
.

Thanks for the quick reply Sam

Pushing the logic back to base measure makes sense. I am not sure I am executing this properly because I am getting the same result as before. Here is the setup:

Total Sales = IF(ISBLANK(SUM(Sales[AmountExtendedPrice])),BLANK(), SUM(Sales[AmountExtendedPrice]))

Customer Rank = 
IF([Total Sales] <= 0,BLANK(),
    RANKX(
        FILTER(ALL(Customer[Customer Name]), NOT(ISBLANK([Total Sales]))),
            [Total Sales], , ASC) )

Customer Sales = 
VAR RankingDimension = VALUES( Sales[SoldToCustomerSKey] )
VAR TotalCustomers = CALCULATE( COUNTROWS( Customer ), FILTER( ALL( Customer[Customer Name] ), [Total Sales] > 0 ))
VAR CustomerRank = [Customer Rank]

RETURN
CALCULATE( [Total Sales],
    FILTER( RankingDimension,
        COUNTROWS(
            FILTER( 'Customer Groups',
                CustomerRank > TotalCustomers * 'Customer Groups'[Low] 
                && CustomerRank <= TotalCustomers * 'Customer Groups'[High] ) ) > 0 ) )

[sam.mckay]

I think it’s quite simple here.

Change to this

Total Sales = IF(SUM(Sales[AmountExtendedPrice]) = 0,BLANK(), SUM(Sales[AmountExtendedPrice]))

Sorry might have lead you astray with my wording, but this seems to get it done.

[dkettinger]

Awesome, works like a charm!

Thank you, and sorry in advance for my barrage of questions that is sure to follow. This site is great for people like me that don’t have a lot of people to help think through some of these DAX issues. This is well worth the membership price alone.

[sam.mckay]

That’s great.

Sounds good.

Chrs