Forum Discussion
Anonymous
3 years agoNot applicable
Hierarchical Ranking Positive to Negative values
Hello pbi community, My question may have an easy solution, but I haven't been able to figure it out. Essentially I wan to display a ranking in a hierarchical form rolling a value from its res...
Anonymous
3 years agoNot applicable
Sorry for not posting a sample dataset before.
Here is a sample data for Locations and Income tables;
Location Table:
| Region | Store | Store Number |
| California Bay Area | Newark | 1 |
| California Bay Area | North - San Jose | 2 |
| California Bay Area | Windsor | 3 |
| California Bay Area | Fairfield | 4 |
| California Bay Area | Oakland | 5 |
| California Bay Area | Richmond | 6 |
| California Bay Area | American Canyon | 7 |
| Canada Alberta | Kelowna | 8 |
| Canada Alberta | Edmonton | 9 |
| Canada Alberta | Calgary 52nd St | 10 |
| Canada Alberta | Calgary | 11 |
| Central Valley | Salinas SJP | 12 |
| Central Valley | Modesto | 13 |
| Central Valley | Stockton | 14 |
| Central Valley | Moss Landing | 15 |
| Central Valley | Merced | 16 |
| Central Valley | Fresno | 17 |
| Central Valley | Salt Lake City | 18 |
| North Mid West NE | Columbus | 19 |
| North Mid West NE | Summit | 20 |
| North Mid West NE | Kansas City-12th Street | 21 |
| North Mid West NE | Kansas City-Winner Road | 22 |
| North Mid West NE | Cumberland | 23 |
| North Mid West NE | Hammond | 24 |
| North Mid West NE | Kansas City | 25 |
| North Mid West NE | Johnston | 26 |
| Sacramento Valley/Nevada | Rocklin | 27 |
| Sacramento Valley/Nevada | Antelope | 28 |
| Sacramento Valley/Nevada | Carson City | 29 |
| Sacramento Valley/Nevada | Chico | 30 |
| Sacramento Valley/Nevada | Redding | 31 |
| Sacramento Valley/Nevada | Rancho Cordova | 32 |
| Sacramento Valley/Nevada | Sparks | 33 |
| Sacramento Valley/Nevada | Sacramento Mather | 34 |
| South Mid West Texas | Elliot Reeder-Ft. Worth | 35 |
| South Mid West Texas | Dallas South | 36 |
| South Mid West Texas | Little Rock | 37 |
| South Mid West Texas | Dallas South Central | 38 |
| South Mid West Texas | Springfield | 39 |
| South Mid West Texas | Virginia Beach | 40 |
| South Mid West Texas | San Antonio | 41 |
| South Mid West Texas | Tallahassee | 42 |
| South Mid West Texas | St Louis | 43 |
| Washington Oregon | Tacoma | 44 |
| Washington Oregon | Arlington | 45 |
| Washington Oregon | Tumwater | 46 |
| Washington Oregon | Lynnwood | 47 |
| Washington Oregon | South Portland | 48 |
| Washington Oregon | Vancouver | 49 |
| Washington Oregon | Sherwood | 50 |
Income table:
| Store Number | Amt |
| 1 | 372748 |
| 2 | 226048 |
| 3 | 49756 |
| 4 | -7825 |
| 5 | -34342 |
| 6 | -253424 |
| 7 | -348797 |
| 8 | -237753 |
| 9 | -283067 |
| 10 | -407082 |
| 11 | -420050 |
| 12 | 73253 |
| 13 | 66690 |
| 14 | 53339 |
| 15 | -56247 |
| 16 | -96195 |
| 17 | -197111 |
| 18 | -197501 |
| 19 | -191547 |
| 20 | -269625 |
| 21 | -323713 |
| 22 | -329673 |
| 23 | -381504 |
| 24 | -385052 |
| 25 | -407029 |
| 26 | -507863 |
| 27 | -124296 |
| 28 | -127783 |
| 29 | -129569 |
| 30 | -203591 |
| 31 | -211544 |
| 32 | -217815 |
| 33 | -237405 |
| 34 | -321198 |
| 35 | 35003 |
| 36 | -18907 |
| 37 | -20411 |
| 38 | -186756 |
| 39 | -199024 |
| 40 | -216219 |
| 41 | -307012 |
| 42 | -338154 |
| 43 | -372394 |
| 44 | -10049 |
| 45 | -63729 |
| 46 | -122354 |
| 47 | -145239 |
| 48 | -180105 |
| 49 | -263666 |
| 50 | -337834 |
both Locations and Income tables joined by Store_Number.
Here are images for what is expected;
Expanded matrix:
Collapsed matrix:
Instead, I'm getting this in Power BI:
I don't understand why the transition from positive to negative 'Operating Income' generates a blank row in the matrix and assignes it a rank = 2.
Dax used to create the measure 'Op Income Rank':
Op Income Rank =
var rank_region =
RANKX(
ALL('Location'[Region]),
[Operating Income], , DESC
)
var rank_store =
RANKX(
FILTER(
ALL('Location'[Store Name], 'Location'[Region]),
'Location'[Region] = MAX('Location'[Region])
), [Operating Income], , DESC
)
return
SWITCH(TRUE(),
ISINSCOPE('Location'[Store Name]), rank_store,
ISINSCOPE('Location'[Region]), rank_region,
BLANK() )
Measure 'Operating Income' was previously calculated, it doesn't present any issue.
I have researched this issue in the web, but no body had this problem before. I may be doing something wrong, but I can not see what is it.
Thanks in advance!