Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

How do i add Ranking to Multiple Rows with the same brand but different models

Hi, 

I am new to Power BI and need your assistance

Basically i have a table in power BI as below which i would like to rank by item based on number of units sold, The only thing is there are multiple rows within the table for each brand with a unique model, when i try to rank this it does not provide me with the correct Ranking as well as Top 20 ranking for any selected period.

Brand                     Item                  Sales units

Brand A                   xx                            25

Brand A                   ff                             28

Brand B                   rr                             45

Brand C                   tt                             02

Brand C                   jj                              85

I would like to Rank the above but based on the highest units sold and then at any point it should also rank by Brand if i select a specific brand.

Your assistance is greatly appreciated.

Need Help
Show and Tell

4 Replies

  • TCarrasquillo's avatar
    TCarrasquillo
    Helper II
    7 years ago

    Hi,

     

    It would be helpful if you posted the formula/measure you were using for ranking, but I think the first thing to try would be to use "ALLSELECTED" instead of "ALL" if you are not already.

     

    I hope that works but if not, try showing your formula. :)

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi, thank you for responding. 

      This is my initial formula

      Rank Units Sold =
      RankX (All('Table'),
      CALCULATE(SUM('Table'[Sales Units])),,DESC,Dense)
       
      Now i tried adding allselected but its still giving me Ranking For each brand and not outlining the Individual ranking for each item.

      I trust this makes sense, as in addition i would like this to dynamically change when i select a different Business Unit(Segment)
       
      Thanks  
      • v-juanli-msft's avatar
        v-juanli-msft
        Community Support
        7 years ago

        Hi Anonymous 

        As tested, with this formula, 

        Measure 2 = RANKX(ALLSELECTED(Sheet2),CALCULATE(SUM(Sheet2[Sales units])),,DESC)

        It is ranked for each item, when i select a brand, it will rank for each item of the brand.

        How about writing another meausre

        Measure = SUM(Sheet2[Sales units])

        Then use this measure in the rank formula

        Measure 2 = RANKX(ALLSELECTED(Sheet2),[Measure],,DESC)

        If it doesn't help, please show an screenshot of yours,

        or download my pbix from my previous reply, based on my pbix, tell me what's wrong i make to understand your requirement, give the correct rank result you expected.

         

        Best Regards

        Maggie

         

        Community Support Team _ Maggie Li
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi Anonymous 

    "I would like to Rank the above but based on the highest units sold and then at any point it should also rank by Brand if i select a specific brand."

    From above, it seems you want to rank sales units by brand on descending order.

    To get this, Create measures

    Measure = SUM(Sheet2[Sales units])

Measure 2 = RANKX(ALLSELECTED(Sheet2),[Measure],,DESC)

    To show Top n rank, create a Top N table without being connected with your data table, then create a measure and add this meausre in the visual level filter of the table visual.

    Measure 3 = IF([Measure 2]<=SELECTEDVALUE('Top N'[Top N]),1,0)

     

    Download my pbix and see more details.

     

    Best Regards

    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.