Forum Discussion

Raymo3u99's avatar
Raymo3u99
Helper I
7 years ago
Solved

Show multiple values based upon a single value selected in a slicer

I have a fairly simple issue.

 

My table has companies and ratings. I also have a rank to order the ratings.

 

company sp_rating sp_rank
test CO AA+ 2
test CO2 AA+ 2
test CO AA 3
test CO2 AA 3
test CO AA- 4
test CO2 AA- 4
test CO A+ 5
test CO2 A+ 5
test CO A 6
test CO2 A 6
test CO A- 7
test CO2 A- 7
test CO BBB+ 8
test CO2 BBB+ 8

 

I want to display all companies with a rank less than or equal to the rating selected in the slicer.

 

Thanks for your help.

 

Raymond

Need Help

10 Replies

  • parry2k's avatar
    parry2k
    Super User
    7 years ago

    Raymo3u99  did you tried changing the rank slicer to less than equal to, you can do it by following steps:

     

    - drop slicer visual and "rank" column as field

    - click drop down on slicer, change it to "less than or equal to"

     

     

    • Raymo3u99's avatar
      Raymo3u99
      Helper I
      7 years ago

      Sorry if I misunderstood you. I want the slicer to show the rating. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    HI,

     

    According to me, Follow these steps

     

    create a new table with a single column of all distinct values of Brand, and make sure there is no relationship between the new table and existing sheet.

     

    Table = DISTINCT(Sheet1[Brand])

    then the formula below should work in your scenario

     

    Rank2 = 
IF (
    HASONEVALUE ( 'Table'[Brand] ),
    VAR selectedSales =
        CALCULATE (
            [SumSales],
            FILTER ( ALL ( Sheet1 ), Sheet1[Brand] = VALUES ( 'Table'[Brand] ) )
        )
    RETURN
        IF (
            [SumSales] <= selectedSales,
            RANKX ( FILTER ( ALL ( Sheet1 ), Sheet1[Sales] <= selectedSales ), [SumSales] ),
            -1
        ),
    RANKX ( ALL ( Sheet1[Brand] ), [SumSales] )
)

     

    Top5 =
IF (
    [Rank2] <= 4
        && [Rank2] > 0,
    [SumSales],
    IF (
        HASONEVALUE ( Sheet1[Brand] ),
        IF (
            VALUES ( Sheet1[Brand] ) = "Others",
            SUMX ( FILTER ( ALL ( Sheet1[Brand] ), [Rank2] > 4 ), [SumSales] )
        )
    )
)
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    Hi,

     

    So if you select a rating of AA- in the slicer, then which rows do youw ant to see?

    • Raymo3u99's avatar
      Raymo3u99
      Helper I
      7 years ago

      Ashish_Mathur  So if the user selects AA-, he should see all companies with a rank greater or equal to 4:

       

      test CO AA- 4
      test CO2 AA- 4
      test CO A+ 5
      test CO2 A+ 5
      test CO A 6
      test CO2 A 6
      test CO A- 7
      test CO2 A- 7
      test CO BBB+ 8
      test CO2 BBB+ 8