Ranking help based on hierarchy

DAX DAX Calculations
1

Hello @Harsh

I have follow up question on Rankx video based on hierarchy posted on YouTube by you,
There is a scenario where I’m trying to get the rank of Top 3 by Products and within that top3 products top 3 sales person which am able to get it successfully but the problem am facing is when I expand the table am getting all the product listed which is incorrect , can you please help me to understand what mistake I have made in my DAX formula.

I have attached PBIX file for your reference.

Image 1 - Top 3 from table without expanding.

image

Image 2 - Top 3 Table after expansion which is displaying all the products.

image

Thank you for your help in Advance.
Customer attrition.pbix (446.8 KB)

1 Like
2

Hi @Dharma,
Thank you very much for posting your query in the forum.
To ensure that when displaying the hierarchy by Product and SalesPeople, Products with a Blank value do not appear, I have created the following measures:

TopX Sales by Product = 
VAR _TopX =
SELECTEDVALUE( 'Rank Values'[Rank No] ) 
VAR _Product_Rank = SUMX(
    ADDCOLUMNS(
        SUMMARIZE( Sales, Products[Product Name] ),
        "@Product Name", RANKX(
            ALL( Products[Product Name] ),
            [Total Sales],,
            DESC
        )
    ),
    [@Product Name]
) 
VAR _Result = IF(
    _Product_Rank <= _TopX,
    [Total Sales],
    BLANK()
) 

RETURN
_Result

TopX Sales by SalesPeople = 
VAR _TopX =
SELECTEDVALUE( 'Rank Values'[Rank No] ) 
VAR SalesPeople_Rank = SUMX(
    ADDCOLUMNS(
        SUMMARIZE( Sales, Salespeople[Salesperson Name] ),
        "@SalesPerson Name", RANKX(
            ALL( Salespeople[Salesperson Name] ),
            [Total Sales],,
            DESC
        )
    ),
    [@SalesPerson Name]
) 
VAR _Result = IF(
    SalesPeople_Rank <= _TopX,
    [Total Sales],
    BLANK()
) 

RETURN
_Result

Rank Test = 
VAR _TopX =
SELECTEDVALUE( 'Rank Values'[Rank No] ) 

VAR _Product_Rank = CALCULATE(
    [TopX Sales by Product],
    ALL( Salespeople[Salesperson Name] )
)

VAR _Dynamic_SalesPerson = IF(
    HASONEVALUE( Products[Product Name] ) &&
    HASONEVALUE( Salespeople[Salesperson Name] ),
    IF(
        NOT ISBLANK( _Product_Rank ),
        [TopX Sales by SalesPeople],
        BLANK()
    ),
    [TopX Sales by SalesPeople]
) 

VAR _Result = SWITCH(
    TRUE(),
    ISINSCOPE( Salespeople[Salesperson Name] ),
    _Dynamic_SalesPerson,
    ISINSCOPE( Products[Product Name] ),
    [TopX Sales by Product],
    BLANK()
) 

RETURN
_Result

When we are showing the hierarchy by Product and SalesPeople, we show the ranking for the Salespeople when the Product ranking is not blank.

I hope it can be of help to you.

Regards

Customer attrition_JAFP.pbix (445.1 KB)

3 Likes