Forum Discussion

AC23VM's avatar
AC23VM
Icon for Helper II rankHelper II
1 year ago

Top 5 values to apply to visuals

Hi there.  Could anyone please suggest a way to capture top 5 values?  I'm having no luck whatsoever with Copilot - it keeps generating measures with errors that fundamentally don't work. 

 

I'm looking at a column called LLRC, with JI as the volume.  Looking at January's data, if I make a table and orient top to bottom values, I get: 15, 15, 12, 9, 7

 

I've tried the Top N filter, but I don't understand how it works.  If I make a new table and use the Top N filter, and look for top 5 LLRC by LLRC, I get: 15, 4, 3, 2, 1.  If I choose Lower LLRC by JI, I get: 9, 4, 1, 1, 1

 

The red volumes are correct, but that table then shows every LLRC data point with a scroll bar, not just the top 5.  Ideally, I'd look to show this in a visual, not just a table or matrix.  Any advice would be greatly appreciated.

Need Help

6 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    1 year ago

    AC23VM , Can you use Rank or Rownumber and use that in visual level filter . Both Rank and rownumber can break ties easily

     

    example

     

    New Rank Desne = rank(DENSE,ALLSELECTED(Sales[Order No]),ORDERBY([Net],DESC))
    New Rank Skip = rank(SKIP,ALLSELECTED(Sales[Order No]),ORDERBY([Net],DESC))
    New Row Number = ROWNUMBER(ALLSELECTED(Sales[Order No]),ORDERBY([Net],DESC))
    Rank Old Dense = rankx(ALLSELECTED('Sales'[Order No]), [Net],,DESC,Dense)
    Rank Old Skip = rankx(ALLSELECTED('Sales'[Order No]), [Net],,DESC,Skip)

     

    Break ties

     

    New Rank Desne = rank(DENSE,ALLSELECTED(Sales[Order No]),ORDERBY([Net],DESC,Sales[Order No], asc ))

     

    Rownumber- https://www.youtube.com/watch?v=yS9-IQjUDwg&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L&index=1

     

    Power BI - New DAX Function: RANK - How It Differs from RANKX: https://youtu.be/TjGkF44VtDo

    • AC23VM's avatar
      AC23VM
      Icon for Helper II rankHelper II
      1 year ago

      Hi there.  Could you please explain a little more about tie breaks?  I've used the following:

      LowerLevelRootCauseCount =
      CALCULATE(
          COUNTROWS('Outcomes Testing Data'),
          ALLEXCEPT('Outcomes Testing Data', 'Outcomes Testing Data'[Lower Level Root Cause], 'Calendar'[Start of Month])
      )

      LowerLevelRootCauseRank =
      RANKX(
          ALLSELECTED('Outcomes Testing Data'[Lower Level Root Cause]),
          CALCULATE(
              COUNTROWS('Outcomes Testing Data'),
              ALLEXCEPT('Outcomes Testing Data', 'Outcomes Testing Data'[Lower Level Root Cause], 'Calendar'[Start of Month])
          ),
          ,
          DESC,
          DENSE
      )

      I then filtered >=1 and <=5, but since my 'top 2' and 'top 4' values are both 15 and 7 respectively, I end up with 7 values: 15, 15, 12, 9, 7, 7, 6.  I should note, I've tried with our unique identifier column, but it's text and doesn't seem to want to convert to numbers.
      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        HI AC23VM,

        For the ranking of these duplicate value, I'd like it suggest you add a custom field with random small values(e.g less than 0.00001).
        Then you can rank on the result value that sum of raw field and the new field values. They should help you to prevent the duplicate scenario.

        Regards,

        Xiaoxin Sheng

  • FarhanJeelani's avatar
    FarhanJeelani
    Icon for Super User rankSuper User
    1 year ago

    Hi AC23VM ,

    Option 1: Use Top N Filter (Simplified):
    1. Select your visual (e.g., bar chart or table).
    2. Go to the Filters pane.
    3. Drag the LLRC field to the Filters on this visual section.
    4. Select Top N.
    5. Enter 5 as the value.
    6. Drag JI into the By value box.
    7. Apply the filter.

     

    Option 2: Create a DAX Measure for Top 5

    Try this DAX measure:

    Top5_LLRCS =
IF(
RANKX(ALLSELECTED('Table'[LLRC]), CALCULATE(SUM('Table'[JI]))) <= 5,
SUM('Table'[JI])
)

    - Apply this measure to your visual and ensure LLRC is in the axis or rows.

     

    Please mark this post as solution if it helps you. Appreciate Kudos.