- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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):
but this is giving rank to blank and 0 as same rank.
Thanks in Advance!
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks @lbendlin for your response,
It's working perfectly. Really appreciate it.
Also, I found another way to resolve this :
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Helpful resources
Power BI Monthly Update - July 2025
Check out the July 2025 Power BI update to learn about new features.

User | Count |
---|---|
6 | |
6 | |
3 | |
2 | |
2 |
User | Count |
---|---|
6 | |
5 | |
4 | |
4 | |
3 |