Dropdown List for Slicer using Column and Measure

[nadeemrajabali]

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 create Ranking for Item name by Total cost. however Ranking by Brand is working fine.

Below are my all measures:

Total Cost :

Total Cost = 'Key Measures'[Total Purchases (AUD)] + 'Key Measures'[Total Landed Cost]

Brand Ranking by Total Cost:

Brand Ranking by Total Cost =

RANKX (

ALL ( Brand[Brand Name] ),

[Total Cost],

,

DESC,

Dense

)

Required:

1) Item ranking by Total Cost, columns available are Item number, Item Name

2) I want a Dynamic list that is combined as : Item Name | Total Cost | Rank this list will be selected using a selection paramater of Top 5 , Top 10 or Top 15

I want to put this list in a single select Slicer that will dictate one of my area chart to show break down of total cost.

Hope this make sense.

Thanks

Nadeem

[v-kelly-msft · Accepted solution]

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!

[VahidDM]

Hi   @nadeemrajabali

Try this:

Brand Ranking by Total Cost =
RANKX (
    FILTER ( Brand, Brand[Item Name] = EARLIER ( Brand[Item Name] ) ),
    [Total Cost],
    ,
    DESC,
    DENSE
)

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!

[nadeemrajabali]

@VahidDM  Thanks for your reply.

My Brand Ranking is working correctly. I am having issues with ranking my items (Products) below is the snip of Model 

So once this ranking is resolved, I want to create the slicer list by combining  Item Name column, Total Cost Measure and Item Rank Measure.

Hope this make sense.

[v-kelly-msft]

Hi  @nadeemrajabali ,

Could you pls provide some dummy data for test?

Remember to remove the confidential information.

Best Regards,
Kelly

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

[nadeemrajabali]

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]

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]

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]

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]

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.

[v-kelly-msft · Accepted solution]

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 · Accepted solution]

Awesome, thanks Kelly highly appreciated

[v-kelly-msft]

Hi  @nadeemrajabali ,

Glad to help.🙂

Best Regards,
Kelly

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