Forum Discussion
Solved
How to assign Rank for zero but exclude null or blank values
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!
but this is giving rank to blank and 0 as same rank.
Thanks in Advance!
4 Replies
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.
