topic Display Top 10 Items with Same Characteristic as Slicer Selection in DAX Commands and Tips

Display Top 10 Items with Same Characteristic as Slicer Selection

msmays5 — Sat, 09 May 2020 15:48:04 GMT

Hi everyone, thanks in advance for your help. I'm not sure if this is even possible via DAX, but here we go:

Background:

I have a Sales table with columns [Markets], [Product Category], [Product Department], and [Dol Sales]. For each department there are multiple categories, but for each category just one department.

I have a slicer that has my list of categories (only one category will be selected)

I'm looking to do the following for a given market:

1. Find the department of the category selected in the slicer

2. Find the top 10 categories by Dol Sales within that department and display them in a chart. If the category selected in the slicer is not in the top 10, display it as well

No idea if this is possible, but I appreciate all of your help

Re: Display Top 10 Items with Same Characteristic as Slicer Selection

mahoneypat — Sat, 09 May 2020 17:34:39 GMT

Once you pick a value in a slicer, your table visual will filter to just that category. Two immediate thoughts are:

1. Add Category and Department slicers. When you pick a category, Department slicer will filter too. Click that department, then uncheck category. Create table visual with a TopN type measure or use the TopN filter in Filter Panel. Not ideal for the user, I know.

2. Disconnected table approach (recommended) - reference your intial table in query editor, remove all but Category and Department columns, remove duplicates on Category. Load that table but do not make a relationship to your Sales table. Then use the Category column from that new table as your slicer, but the original Category column in your table visual. Then use a measure like this:

New Measure = var selecteddepartment = selectedvalue(SlicerTable[Department])

return calculate(sum(Sales[Amount]), treatas(values(SlicerTable[Department]), Sales[Department]))

Add a TopN filter on the table visual for Department with same measure (or add TopN clause to the measure).

Re: Display Top 10 Items with Same Characteristic as Slicer Selection

msmays5 — Sat, 09 May 2020 23:54:30 GMT

Disconnected table makes perfect sense. Thanks!