locked
RANKS BASED ON MEASURE RRS feed

  • Question

  • Dear All

    My pivot table looks like this:

    Row Labels VALUE SHARE %
    SAMSUNG 39,2%
    LG 21,4%
    SONY 8,3%
    PANASONIC 7,9%
    PHILIPS 7,0%
    TOSHIBA 3,8%
    SHARP 3,4%
    <TRADEBRAND &   EXCLUSIVE> 1,4%
    THOMSON 1,0%
    GRUNDIG 0,7%
    SENCOR 0,7%
    HYUNDAI 0,7%
    FUNAI 0,6%
    ORION 0,6%
    BLAUPUNKT 0,4%
    TELEFUNKEN 0,2%
    HITACHI 0,2%

    ....                                                              

    What I need is to add rank and get the following pivot:

    RANK Row Labels VALUE SHARE %
    1 SAMSUNG 39,2%
    2 LG 21,4%
    3 SONY 8,3%
    4 PANASONIC 7,9%
    5 PHILIPS 7,0%
    6 TOSHIBA 3,8%
    7 SHARP 3,4%
    8 <TRADEBRAND & EXCLUSIVE> 1,4%
    9 THOMSON 1,0%
    10 GRUNDIG 0,7%
    11 SENCOR 0,7%
    12 HYUNDAI 0,7%
    13 FUNAI 0,6%
    14 ORION 0,6%
    15 BLAUPUNKT 0,4%
    16 TELEFUNKEN 0,2%
    17 HITACHI 0,2%
    18 GOGEN 0,2%
    19 MANTA 0,2%
    20 VORTEX 0,2%

    ...

    "VALUE SHARE%" is a measure.

    VALUE SHARE %:=SUM('PTV FLAT HITLIST'[SALES THS#EUR])/CALCULATE(SUM('PTV FLAT HITLIST'[SALES THS#EUR]);ALL('PTV FLAT HITLIST'[BRAND]))

    Data model (key part of bigger table) looks like this:

    Table: PTV FLAT HITLIST

    How to write a measure to get the Rank of brands?

    I tried this but not working:

    Measure 1:=RANK.EQ('PTV FLAT HITLIST'[SALES THS#EUR];'PTV FLAT HITLIST'[BRAND];1)

    also this not working:

    Measure 2:=RANKX('PTV FLAT HITLIST';'PTV FLAT HITLIST'[BRAND];'PTV FLAT HITLIST'[SALES THS#EUR];1)

    Gordonik



    Tuesday, October 21, 2014 2:22 PM

Answers

  • Not sure exactly on what the final product will look like, but try these:

    Measure1:=
    RANKX(
        ALL( 'PTV FLAT HITLIST'[BRAND] )
        ; [VALUE SHARE %]
    )
    
    Measure2:=
    RANKX(
        VALUES( 'PTV FLAT HITLIST'[BRAND] )
        ; [VALUE SHARE %]
    )

    As you can see, these two measures only differ in the table function used, ALL() and VALUES().

    ALL() will ignore any filter context on 'PTV FLAT HITLIST'[BRAND] and return only unique values from that table. It will respect filter context on other attributes in that table

    VALUES() will respect filter context on the field (As well as the rest of the table) and return unique values in context.

    If you need to filter on other attributes of 'PTV FLAT HITLIST'[BRAND], but still maintain the overall ranking, then you'll need a different solution:

    Measure1:=
    RANKX(
        CALCULATETABLE(
            VALUES( 'PTV FLAT HITLIST'[BRAND] )
            , ALL( 'PTV FLAT HITLIST' )
        )
        ; [VALUE SHARE %]
    )

    Tuesday, October 21, 2014 4:47 PM

All replies

  • Not sure exactly on what the final product will look like, but try these:

    Measure1:=
    RANKX(
        ALL( 'PTV FLAT HITLIST'[BRAND] )
        ; [VALUE SHARE %]
    )
    
    Measure2:=
    RANKX(
        VALUES( 'PTV FLAT HITLIST'[BRAND] )
        ; [VALUE SHARE %]
    )

    As you can see, these two measures only differ in the table function used, ALL() and VALUES().

    ALL() will ignore any filter context on 'PTV FLAT HITLIST'[BRAND] and return only unique values from that table. It will respect filter context on other attributes in that table

    VALUES() will respect filter context on the field (As well as the rest of the table) and return unique values in context.

    If you need to filter on other attributes of 'PTV FLAT HITLIST'[BRAND], but still maintain the overall ranking, then you'll need a different solution:

    Measure1:=
    RANKX(
        CALCULATETABLE(
            VALUES( 'PTV FLAT HITLIST'[BRAND] )
            , ALL( 'PTV FLAT HITLIST' )
        )
        ; [VALUE SHARE %]
    )

    Tuesday, October 21, 2014 4:47 PM
  • Hi Greg

    The measure with "VALUE" not working corectly:

    Row Labels RANK PTV VALUE SHARE   %
    SAMSUNG 1 39,2%
    LG 1 21,4%
    SONY 1 8,3%
    PANASONIC 1 7,9%
    PHILIPS 1 7,0%
    TOSHIBA 1 3,8%
    SHARP 1 3,4%
    <TRADEBRAND &   EXCLUSIVE> 1 1,4%
    THOMSON 1 1,0%
    GRUNDIG 1 0,7%
    SENCOR 1 0,7%
    HYUNDAI 1 0,7%
    FUNAI 1 0,6%
    ORION 1 0,6%
    BLAUPUNKT 1 0,4%
    TELEFUNKEN 1 0,2%
    HITACHI 1 0,2%
    GOGEN 1 0,2%
    MANTA 1 0,2%
    VORTEX 1 0,2%
    TELETECH 1 0,2%
    AKAI 1 0,2%
    TCL 1 0,2%
    OTHERS 1 0,1%
    CHANGHONG 1 0,1%
    ECG 1 0,1%
    ORAVA 1 0,1%
    HORIZON 1 0,1%

    but the first one is correct. Thanks a lot

    G

    Wednesday, October 22, 2014 8:40 AM
  • VALUES() returns unique elements of the field (or table) based on current filter context. In the case of 'PTV FLAT HITLIST'[BRAND] on the pivot rows, the filter context in any cell is restricted to the context of the current rowfilter.

    Glad to hear that ALL() is fulfilling your needs.

    Wednesday, October 22, 2014 4:49 PM