topic Re: How to assign Rank for zero but exclude null or blank values in Developer

How to assign Rank for zero but exclude null or blank values

BI_Prachi — Wed, 13 Sep 2023 23:48:20 GMT

Hi,
I have the following tables
Dim :

LocationCodeName	Locationcode
A	A001
B	B001
C	C001
D	D001
E	E001
F	F001

SalesFact :

Locationcode	Qty
A001	8
B001	20
E001	60
F001	40
C001	22

Target:

Locationcode	Target
A001	10
B001	20
E001	30
F001	40
C001	20

Target % = Divide(Sales- Target , Target) *100
So i need to calculate rank as follows:

LocationCodeName	Qty	Target	Target %	Rank
A	8	10	-20	4
B	20	20	0	3
C	22	20	10	2
D
E	60	30	100	1
F	40	40	0	3

Rank should be given based on highest to lowest target% including 0 (which means the location has met its target completely - Location B001 and F001 in this case) however excluding non-existing location (means no target was given to these location in this month - Location D001 in this case).

I tried the following DAX formula but it doesnt work for null values (where there was no target given):

Location Ranking =

RANKX(All('dim'[LocationCodeName])

CALCULATE( [# Target %]),,desc,dense

)
but this is giving rank to blank and 0 as same rank.

Thanks in Advance!

Re: How to assign Rank for zero but exclude null or blank values

lbendlin — Fri, 15 Sep 2023 01:22:04 GMT

Re: How to assign Rank for zero but exclude null or blank values

BI_Prachi — Fri, 15 Sep 2023 10:06:34 GMT

Thanks @lbendlin for your response,
It's working perfectly. Really appreciate it.

Also, I found another way to resolve this :

if(NOT(ISBLANK([Target %])), rankx(FILTER(ALL('dim'[LocationCodeName]),NOT(ISBLANK([Target]))),CALCULATE([Target %]),,,Dense),BLANK())

Re: How to assign Rank for zero but exclude null or blank values

lbendlin — Fri, 15 Sep 2023 13:53:14 GMT

If you are interested you can run both versions of this through DAX Studio and compare the query plans and server timings.

CALCULATE([Target %]) seems redundant.

Re: How to assign Rank for zero but exclude null or blank values

BI_Prachi — Thu, 21 Sep 2023 09:20:31 GMT

Yeah, you are right @lbendlin . Much appreciated. Thanks again 🙂