Don't Rank with Blank Field Value in column

[chotu27]

Hi All,

I wanted to rank only for category which have names not for all.

Data:

Category	Sales
Electronics	556
Pharmacy	88
Bakery	999
	800

Expected Output:  

Category	Sales	Rank
Electronics	556	2
Pharmacy	88	3
Bakery	999	1
	800

DAX Measure Used (Rank) = 

RANKX(ALL('Table'[Category]), CALCULATE(SUM('Table'[Sales]), ALLEXCEPT('Table','Table'[Category])))

Can someone Help me to Rank only for the catgeory which has names.

Note: I don't want to use any filter in transform editor i wanted to see the blank names but not the rank

[RasmusN532]

I am having the same problem - did anyone find a solution to this ? 🙂

[danextian]

Hi @chotu27 ,

You can add another argument in your CALCULATE to exclude category items that are blank (assuming they're indeed blank/null). Try this:

DAX Measure Used (Rank) =
RANKX (
    ALL ( 'Table'[Category] ),
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        ALLEXCEPT ( 'Table', 'Table'[Category] ),
        FILTER ( 'Table', 'Table'[Category] <> BLANK () )
    )
)

Alternatively, you can open go to: filter pane > visual filter tile > category > advanced filter > is not blank.

[chotu27]

@danextian 

I'm getting all 1's when i use your measure for ranking,

and visual level filter will remove blank fields but it will rank blank field also i wanted exclude blanks while ranking