Multiple Levels/Drills Rank Measure (Problem)

[Ciria]

Dears:

I'm experiencing  some problems with a Matrix created with several levels/drills in rows and a measure to Rank data in every single level.

For the moment I'm just defining three levels, Family, Customer Country and Item Code. Current design allows to have a quick overview about the main figures per family, having the possibility to drill down per country and Code at will.

This is how the first level looks like:

Here a standard Rank Calculation has been used:

Rank = RANKX(ALL(Master_Item_All[Family]);[Total Sales];;DESC)

After drilling: You will notice two problems here:

1. Ranking is not properly done, not ranking correctly.
2. All countries are shown, not only those ones with values in a column.

Here is how relathionships are built, find my remarks down below:

• All tables (many) are named as Fact.
• Dimensional tables are not named properly, but are located above following waterfall scheme.
• Two dimensional tables have been moved down to facilitate understanding.
• Family field belongs to Master_Item_All Table (Dimensional), where Item_Number is the unique value, being "Family" a multiple value consequently (many Item_Numbers may belong to same family).
• I can't upload the original file for personal reasons.

     

I was trying to use different approaches, like SUMMARIZE, ADDCOLUMNS, IF, but nothing works, so I hope you may provide your experience on this to get multiple levels drill down, ranking correctly and only showing rows with values.

Rank = 
IF(HASONEFILTER(Master_Item_All[Family]);
    RANKX(ALL(Master_Item_All[Family]);[Total Sales];;DESC;Skip);
        RANKX(ALL(Master_Customer_Suppliers[Country_Name]);[Total Sales];;DESC;Skip))

Thanks in advance,

[v-yulgu-msft]

Hi @Ciria,

Here is a blog for your reference:

DAX – Ranking with Hierarchy Drill-Down Problem

Best regards,

Yuliana Gu

[Ciria]

Hi @v-yulgu-msft

First of all thank you for the answer, it is really helpful. It has solved 50% of my problem.

Now, I can drill down in multiples levels getting proper ranking results. The good thing is this method  makes drill down possible even in multiple levels, not only two, opening a wide range of opportunities by using IF and NOT functions.

But still the second problem I mentioned is present.

1. When I click over a visual, narrowing down the list on table, all families/countries are shown, even when they don't have any result to show up.
2. Same thing when I drill down in one family, Table is not showing me only those results with values, but all countries, regardless they have sales or not.
3. I've attached two pictures for further explanation.

How can amend the formula to only show values with figures?

Using VALUES instead of ALL in RANKX function doesn't work.... Perhaps a combination of FILTER+VALUES like @EnterpriseDNA (Sam Mckay did here) but my DAX skills are still limited 😞

https://www.youtube.com/watch?v=IyrNH7aD_qk&t=408s

Awaiting for your feedback 🙂

[Ciria]

Dears:

By the way, here the Dropbox's Link to my file with the formula test done.

https://www.dropbox.com/s/88dtadb0s7m1hpd/Dummy%20Model.pbix?dl=0

Regards,

[Ciria]

Dears:

Any idea about how remove those countries without sales on the list?

You need ALL function there to remove the filter context you have with "Country_Name" on Rows, otherwise all rows will be ranked as "1".

I have temporaly managed the situation, using "Visual Filters", selecting "Total Sales Greater than 0", but I would like to know if there is a way to do it with DAX.

Thanks in advance for the support 🙂

[Anonymous]

Hello 

Is there a solution for this yet? tried the examples in the above PBIX and Youtube video but no luck. 

I've got the "Sub category" Ranking to work funnily enough but not the main category.

My DAX is: 

Index Rank =
VAR IsCountryFiltred =
    ISFILTERED ( Country[Country] )
VAR IsTitleSeasonFiltered =
    ISFILTERED ( Programme[Title Season] )
VAR IsTitleSeason =
    HASONEVALUE ( Programme[Title Season] )
RETURN
    IF (
        ISBLANK ( [RTG Index] ),
        BLANK (),
        IF (
            IsCountryFiltred && IsTitleSeasonFiltered,
            RANKX ( ALL ( Country[Country] ), [RTG Index],, DESC, SKIP ),
            IF (
                IsTitleSeasonFiltered && NOT IsCountryFiltred,
                RANKX ( ALL ( Programme[Title Season] ), CALCULATE ( [RTG Index] ) ),
                BLANK ()
            )
        )
    )

 Any help would be grand. 

Thanks

Sub Category Rank working with DaxMain category only works with I remove the Sub catogory from the Matrix