Forum Discussion
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 respective store then to the region.
The problem I'm facing is that when passing from positive to negative values at a region level, the matrix creates a new blank row and assigns it a rank number.
Below I'm providing images about the issue ;
Here is how the table looks like without the ranking measure;
The ranking measure is working well at a store level except at the strange blank row;
Here is the dax to created to calculate the ranking:
Finally, the data model looks like this, where I'm joining Location and Income tables with Store_number columns;
In advance thank you so much for your help!
2 Replies
- Greg_DecklerCommunity Champion
Anonymous Sample data please. Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2. - AnonymousNot 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!