Display Top 10 Items with Same Characteristic as Slicer Selection

[msmays5]

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

[mahoneypat · Accepted solution]

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).

[msmays5]

Disconnected table makes perfect sense. Thanks!