locked
Rankx RRS feed

  • Question

  • Hi

    In my 1) scenario the measure Rank (Brands CY) working well...

    Rank (Brands CY) =
    IF (
        HASONEVALUE ( FRAGRANCE_PL[BRAND] ),
        RANKX (
            ALL ( FRAGRANCE_PL[BRAND] ),
            CALCULATE (
                SUM ( FRAGRANCE_PL[Sales Value (const$)] ),
                FILTER ( ALL ( FRAGRANCE_PL[YEAR] ), FRAGRANCE_PL[YEAR] = 2015 )
            )
        )
    )

    but in my 2) scenario, it returns not what I expected

    The scenarios:

    I expect to always have a proper ranking for brands.

    Just like this

    :

    How to adjust this measure to work on both scenarios?


    Monday, February 15, 2016 3:54 PM

Answers

  • First, I think your formula can be simplified. The HASONEVALUE seems to do nothing here, and the FILTER expression can be changed to a simple filter:

    Rank (Brands CY):=

    RANKX(ALL(FRAGRANCE_PL[BRAND]),

      CALCULATE(SUM(FRAGRANCE_PL[Sales Value]), FRAGRANCE_PL[Year]=2015)

    )

    (when you want to change the constant 2015 for a measure later on, you do need the FILTER)

    Now, the reason that scenario 2) isn't working as expected is because of the ALL(FRAGRANCE_PL[BRAND]) table in RANKX. In scenario 2, there is a filter on the column [PRODUCT TYPE] as well, and ALL(FRAGRANCE_PL[BRAND]) will return all brands for a specific product type. So what you get is the rank of a brand for each product type (you will probably notice that the same rank shows up multiple times in the pivot table, but never with the same product type).

    The solution is not to use ALL(FRAGRANCE_PL[BRAND],FRAGRANCE_PL[PRODUCT TYPE]) instead, because then you would get the rank over all combinations of brand and product type. My suggestion would be

    CALCULATETABLE(ALL(FRAGRANCE_PL[BRAND]),ALL(FRAGRANCE_PL[PRODUCT TYPE]))

    This way, the existing filter on product type is removed before evaluating the table with all brands.

    BTW, it looks like you have only one table in your model, at least you should go for a separate Calendar table that FRAGRANCE_PL is related to. The filter expression can then be changed into Calendar[Year]=2015.

    Tuesday, February 16, 2016 7:34 AM
    Answerer
  • Finally I end with:

    Rank (Brands CY)2 =
    RANKX (
        ALL ( FRAGRANCE_PL[BRAND]; FRAGRANCE_PL[PRODUCT TYPE] );
        CALCULATE (
            SUM ( FRAGRANCE_PL[Sales Value (const$)] );
            FILTER ( ALL ( FRAGRANCE_PL[YEAR] ); FRAGRANCE_PL[YEAR] = 2015 )
        )
    )

    Which seems to work

    Wednesday, August 10, 2016 1:23 PM

All replies

  • First, I think your formula can be simplified. The HASONEVALUE seems to do nothing here, and the FILTER expression can be changed to a simple filter:

    Rank (Brands CY):=

    RANKX(ALL(FRAGRANCE_PL[BRAND]),

      CALCULATE(SUM(FRAGRANCE_PL[Sales Value]), FRAGRANCE_PL[Year]=2015)

    )

    (when you want to change the constant 2015 for a measure later on, you do need the FILTER)

    Now, the reason that scenario 2) isn't working as expected is because of the ALL(FRAGRANCE_PL[BRAND]) table in RANKX. In scenario 2, there is a filter on the column [PRODUCT TYPE] as well, and ALL(FRAGRANCE_PL[BRAND]) will return all brands for a specific product type. So what you get is the rank of a brand for each product type (you will probably notice that the same rank shows up multiple times in the pivot table, but never with the same product type).

    The solution is not to use ALL(FRAGRANCE_PL[BRAND],FRAGRANCE_PL[PRODUCT TYPE]) instead, because then you would get the rank over all combinations of brand and product type. My suggestion would be

    CALCULATETABLE(ALL(FRAGRANCE_PL[BRAND]),ALL(FRAGRANCE_PL[PRODUCT TYPE]))

    This way, the existing filter on product type is removed before evaluating the table with all brands.

    BTW, it looks like you have only one table in your model, at least you should go for a separate Calendar table that FRAGRANCE_PL is related to. The filter expression can then be changed into Calendar[Year]=2015.

    Tuesday, February 16, 2016 7:34 AM
    Answerer
  • Thanks Michiel

    Following your suggestions I made the Rank (Brands CY)2:

    Rank (Brands CY)2 =
    IF (
        HASONEVALUE ( FRAGRANCE_PL[BRAND] ),
        RANKX (
            CALCULATETABLE (
                ALL ( FRAGRANCE_PL[BRAND] ),
                ALL ( FRAGRANCE_PL[PRODUCT TYPE] )
            ),
            CALCULATE (
                SUM ( FRAGRANCE_PL[Sales Value (const$)] ),
                FILTER ( ALL ( FRAGRANCE_PL[YEAR] ), FRAGRANCE_PL[YEAR] = 2015 )
            )
        )
    )

    I want to keep HASONEVALUE to avoid showing ranks on totals.

    The result of new measure is still not perfect:

    Am i doing sth wrong?


    Tuesday, February 16, 2016 9:34 AM
  • Bartek, any luck with this?

    Please send a Frown to the team or use Power BI User Voice to submit your feedback!

     

    Thanks!


    Ed Price, Azure Development Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!


    Friday, July 29, 2016 8:30 PM
  • Finally I end with:

    Rank (Brands CY)2 =
    RANKX (
        ALL ( FRAGRANCE_PL[BRAND]; FRAGRANCE_PL[PRODUCT TYPE] );
        CALCULATE (
            SUM ( FRAGRANCE_PL[Sales Value (const$)] );
            FILTER ( ALL ( FRAGRANCE_PL[YEAR] ); FRAGRANCE_PL[YEAR] = 2015 )
        )
    )

    Which seems to work

    Wednesday, August 10, 2016 1:23 PM