Looking to Rank Customers by Customer Name but exclude where the Customer Name Field is Blank

[hstgeorge]

I have a data set that looks like the below:

CUSTOMER NAME	VOLUME
CUSTOMER A	250
CUSTOMER B	450
CUSTOMER C	100
CUSTOMER D	340
(BLANK)	750

I want to rank the customers where the customer name is not Blank.

I am currently using the below formula:

rank toggle = RANKX(ALL(SecVol_TD[CHAIN NAME (groups)]),CALCULATE([PY_UCS plus Volume]*1),,DESC,Skip)

But this formula is keeping where the customer name blank so it's ranking the blank ones at 2 - I don't want to rank blank customer names.

[ryan_mayu · Accepted solution]

@hstgeorge 

Please try below measures

total = sum(customer[VOLUME])

rank1 = 
if(SELECTEDVALUE(customer[CUSTOMER NAME])=blank(),"",
 RANKX(ALL('customer'),[total],,ASC))

Hope this is helpful.

[v-yiruan-msft · Accepted solution]

Hi @hstgeorge ,

Please try to update the formula of your measure as below:

rank toggle = 
IF (
    ISBLANK ( MAX ( 'SecVol_TD'[CHAIN NAME (groups)] ) )
        || MAX ( 'SecVol_TD'[CHAIN NAME (groups)] ) = "",
    "",
    RANKX (
        FILTER (
            ALLSELECTED(  'SecVol_TD'[CHAIN NAME (groups)] ),
            NOT ( ISBLANK ( 'SecVol_TD'[CHAIN NAME (groups)] ) )
                && 'SecVol_TD'[CHAIN NAME (groups)] <> ""
        ),
        CALCULATE ( [PY_UCS plus Volume] * 1 ),
        ,
        DESC,
        SKIP
    )
)

Best Regards

Rena

[harshnathani]

Hi @hstgeorge ,

Apart from the solution given below you can try this measure.

Ranking Vol =
IF (
    MAX ( 'Table'[CUSTOMER NAME] )
        <> BLANK (),
    RANKX (
        FILTER (
            ALL ( 'Table'[CUSTOMER NAME] ),
            'Table'[CUSTOMER NAME]
                <> BLANK ()
        ),
        CALCULATE (
            SUM ( 'Table'[VOLUME] )
        )
    )
)

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[ryan_mayu · Accepted solution]

@hstgeorge 

Please try below measures

total = sum(customer[VOLUME])

rank1 = 
if(SELECTEDVALUE(customer[CUSTOMER NAME])=blank(),"",
 RANKX(ALL('customer'),[total],,ASC))

Hope this is helpful.

[mahoneypat]

Have you tried using ALLNOBLANKROW() instead of ALL() in your RANKX expression?

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat