Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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: 

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() )

 

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_Deckler's avatar
    Greg_Deckler
    Community 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.

  • Anonymous's avatar
    Anonymous
    Not applicable

    Sorry for not posting a sample dataset before. 

    Here is a sample data for Locations and Income tables; 
    Location Table:

    RegionStoreStore Number
    California Bay AreaNewark1
    California Bay AreaNorth - San Jose2
    California Bay AreaWindsor3
    California Bay AreaFairfield4
    California Bay AreaOakland5
    California Bay AreaRichmond6
    California Bay AreaAmerican Canyon7
    Canada AlbertaKelowna8
    Canada AlbertaEdmonton9
    Canada AlbertaCalgary 52nd St10
    Canada AlbertaCalgary11
    Central ValleySalinas SJP12
    Central ValleyModesto13
    Central ValleyStockton14
    Central ValleyMoss Landing15
    Central ValleyMerced16
    Central ValleyFresno17
    Central ValleySalt Lake City18
    North Mid West NEColumbus19
    North Mid West NESummit20
    North Mid West NEKansas City-12th Street21
    North Mid West NEKansas City-Winner Road22
    North Mid West NECumberland23
    North Mid West NEHammond24
    North Mid West NEKansas City25
    North Mid West NEJohnston26
    Sacramento Valley/NevadaRocklin27
    Sacramento Valley/NevadaAntelope28
    Sacramento Valley/NevadaCarson City29
    Sacramento Valley/NevadaChico30
    Sacramento Valley/NevadaRedding31
    Sacramento Valley/NevadaRancho Cordova32
    Sacramento Valley/NevadaSparks33
    Sacramento Valley/NevadaSacramento Mather34
    South Mid West TexasElliot Reeder-Ft. Worth35
    South Mid West TexasDallas South36
    South Mid West TexasLittle Rock37
    South Mid West TexasDallas South Central38
    South Mid West TexasSpringfield39
    South Mid West TexasVirginia Beach40
    South Mid West TexasSan Antonio41
    South Mid West TexasTallahassee42
    South Mid West TexasSt Louis43
    Washington OregonTacoma44
    Washington OregonArlington45
    Washington OregonTumwater46
    Washington OregonLynnwood47
    Washington OregonSouth Portland48
    Washington OregonVancouver49
    Washington OregonSherwood50

     

    Income table:

    Store NumberAmt
    1372748
    2226048
    349756
    4-7825
    5-34342
    6-253424
    7-348797
    8-237753
    9-283067
    10-407082
    11-420050
    1273253
    1366690
    1453339
    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
    3535003
    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!