TopN with others - WITHOUT additional table? (SSAS Tabular)

[mark77]

Hi. We use SSAS to prepare our datamodel for our powerbi RS reports.

I've been asked to add a number of "top n with others" visuals.

There are some articles on this. And all seem to require an additional table to be added for each dimension.

This is, ofcourse, rediculous. There should be a tag in the filter screen with "Show Others" as option, like is available in the oldest Qlik products. Simple for end users.

Anyway.

Is there a way to ONLY use DAX to create a top n with others? Without the need to create additional tables for each dimension? 

I cannot find it anywhere. Thank you for your help.

I've reviewed items such as :

Power BI – Dynamic TopN + Others with Drill-Down | Gerhard Brueckl on BI & Data (gbrueckl.at)

Filter top 3 products with a row for others using DAX in Power BI - YouTube

let me add that in this example

my dimension is : Variety[Variety Name]

my fact is : [TurnOver]

Lets set a hardcoded top 10, and skip variables. I can add that.

Thanks!

[lbendlin]

You need to create the additional tables but you don't need to materialize them.  Instead you can use table variables inside the measures. 

Measure = if(RANKX(ALLSELECTED(Variety[VarietyName]),[TurnOver])<11,SELECTEDVALUE(Variety[VarietyName]),"Other")

Please provide sanitized sample data in usable format (not as a picture - inserting it into a table would be good).

[mark77]

Thank you for this first block, but the sollution is not yet complete.

I now indeed have a list of 10 VarietyNames and an other field. But cannot create the desired results.

When using expression [TurnOver]  and just the [NewVarietyTop10] dimension, no data is displayed. Only a total summary line with no description is deplsyaed.

When adding the origional VarietyName, then all varieties are shown. (And the others also become visble in individual rows).

I'm asuming something needs to be done with the table to make it all work? What is the final step?

[lbendlin]

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. Please show the expected outcome.

[mark77]

hi,

I've prepared a sanitized dataset for you with just the case.

please follow this link

ZIP: https://drive.google.com/file/d/1awfmZQLftIELkMLqOg3h9f-WJj6cmlfu/view?usp=sharing

PBIX : https://drive.google.com/file/d/1tx9YfsNV7mgVACR9Hry51E5vh4d9BPNB/view?usp=sharing

I've included the desired result example as a picture, in the file as well.

Thanks again.

[lbendlin · Accepted solution]

You are right, the standard matrix visual would not work for this as there are no rows to tag the measure against.  I am wondering if there are other matrix type visuals that can do that.

I also didn't catch the fact that you are doing this on RS - that may further complicate things as custom visuals are more tricky there.

See attached for a visual, erm, "solution" .

[mark77]

Thank you for your efforts though.
I've added my thumbs up to the feature request at Microsoft Idea  · Add others options to TopN Filter (powerbi.com)