Forum Discussion

nadeemrajabali's avatar
nadeemrajabali
Helper I
4 years ago
Solved

Dropdown List for Slicer using Column and Measure

Hi All, I have a measure that calculate Total Cost of products. The measure is working fine if Financial Year, Brand or any other context is selected. I am new to Power Bi therefore struggling to cr...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    4 years ago

    Hi  nadeemrajabali ,

     

    Sorry for misunderstanding.

    Create a calculated column as below:

    Merged = 'data'[Brand Name]&"|"&'data'[Item Name]&"|"&"$"&'data'[Total Cost]

    Put "Merged" in the field of a slicer and you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my reply as a solution!

  • nadeemrajabali's avatar
    nadeemrajabali
    4 years ago

    Awesome, thanks Kelly highly appreciated

nadeemrajabali's avatar
nadeemrajabali
Helper I
4 years ago

Hi Kelly,

 

Please see below sample data.

 

Product Data CSV 

 

1) I wante to rank all items within Brand in DESC order
2) I want make a slicer where Rank | Item name | Total Cost is coming up as dropdown list. I only want TOP 20 products to be loaded in this slicer depending on my other slicer being selected.

 

 

 

Hope you can help.

 

Thanks

Nadeem

v-kelly-msft's avatar
v-kelly-msft
Community Support
4 years ago

Hi  nadeemrajabali ,

 

Create a measure as below:

Measure =
RANKX (
    FILTER ( ALLSELECTED ( data ), 'data'[Brand Name] = MAX ( 'data'[Brand Name] ) ),
    CALCULATE ( MAX ( 'data'[Total Cost] ) ),
    ,
    DESC,
    DENSE
)

Make a selection in filter pane as below:

 

And you will see:

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

  • nadeemrajabali's avatar
    nadeemrajabali
    Helper I
    4 years ago

    Awesome, thanks Kelly. My half problem is solved.
    The Slicer I am looking for is a list in below format:

    Rank | Item Name | Total Cost

    For example: like below: I can make it in Excel but not sure if it's possible in Power Bi?

     

    This allow the list to be in sorted order as well as gives user a sanity what he/she is selecting from drop down list.

     

     

    • v-kelly-msft's avatar
      v-kelly-msft
      Community Support
      4 years ago

      Hi nadeemrajabali ,

       

      Yes,click the dropdown button on the right upper corner of slicer to change the slicer type>choose: dropdown:

      And you will see:

       

       

      Best Regards,
      Kelly

      Did I answer your question? Mark my reply as a solution!

       

       

      • nadeemrajabali's avatar
        nadeemrajabali
        Helper I
        4 years ago

        Hi v-kelly-msft ,

        Thanks for your help again. But this is not I want in my drop list. I want my list as merged of three columns kind of a dynamic list. The reason I want it like this to have my list sorted as per ranking. In your drop down list. It's sorted by item # also the $ value is not the list.