To display Top 10 + Others for Matrix with multiple filters as slicers

[Anonymous]

Hi Everyone,

Kindly suggest how can we implement 10 + others for matrix which shows calculations for Sales numbers in Matrix
1) Measure 1 - Sales Actual
2) Measure 2 - Sales Previous Year
3) Measure 3 -  Sales Budget

I need to implement Top 10 + Others for above measures in matrix which are sliced by Filters like Country, Account Owner, Client etc 

Thanks,
Radhika

[v-yiruan-msft]

Hi @Anonymous ,

Whether your problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem with you. Thank you.

Best Regards

[v-yiruan-msft]

Hi @Anonymous ,

You can refer the content in the following links to get it:

Filtering the top products alongside the other products in Power BI

Dynamic TopN + Others in Power BI

How To Use Top N + Others Functionality In Power BI

Best Regards

[NidhiBhusari]

Hi @Anonymous , can you share your sample dataset...i have implemented the TOP N + others using my dataset but unable to share the.pbix file here.

[NidhiBhusari]

Hi @Anonymous , below are the steps i have implemented you can refer to these steps and let me know if it is beneficial for you.
1) 
Movie Name = UNION (VALUES ('Onyx Data - DataDNA Dataset Cha'[Movie Title]), ROW( "MovieName", "Others" ))

2) Here I have implemented Top N using profit calc on the basis of Movie Title.
 

Profit calc = CALCULATE(SUM('Onyx Data - DataDNA Dataset Cha'[Profit]), GROUPBY('Onyx Data - DataDNA Dataset Cha','Onyx Data - DataDNA Dataset Cha'[Genre]))

In your case, you need to calculate below three measures separately.

1) Measure 1 - Sales Actual
2) Measure 2 - Sales Previous Year
3) Measure 3 -  Sales Budget

3)Measure to calculate Top N values:-

Top X =

    VAR TopNumber =if(HASONEVALUE('TOP N'[Column1]),VALUES('TOP N'[Column1]),10)

VAR Rest =

COUNTROWS ('Onyx Data - DataDNA Dataset Cha') - TopNumber

RETURN

IF (

HASONEVALUE ( MovieName[Movie Title]),

CALCULATE (

[Profit calc],

FILTER (

'Onyx Data - DataDNA Dataset Cha',

[Movie Title] = VALUES ( MovieName[Movie Title])

&& CONTAINS (

TOPN (

TopNumber,

ADDCOLUMNS (

ALL (MovieName[Movie Title]),

"Profit", CALCULATE (

[Profit calc],

FILTER ( 'Onyx Data - DataDNA Dataset Cha','Onyx Data - DataDNA Dataset Cha'[Movie Title] = EARLIER ([Movie Title]))

)

),

[Profit], DESC

),

MovieName[Movie Title], VALUES ( MovieName[Movie Title])

)

)

)

)

Measure to calculate remaining values:-

Other =

    VAR TopNumber = if(HASONEVALUE('TOP N'[Column1]),VALUES('TOP N'[Column1]),10)

VAR Left =

COUNTROWS ( 'Onyx Data - DataDNA Dataset Cha' ) - TopNumber

RETURN

IF (

HASONEVALUE (MovieName[Movie Title]),

IF (

VALUES ( MovieName[Movie Title]) = "Others",

SUMX (

TOPN (

Left,

ADDCOLUMNS (

VALUES ('Onyx Data - DataDNA Dataset Cha'[Movie Title]),

"Measure", [Profit calc]

),

[Measure], ASC

),

[Measure]

)

)

)

5) At last create a table(here i have used the Name Slicer Table) and add names of measures between which you want to dynamically switch to get values.

SWITCH(TRUE(),

SELECTEDVALUE(Slicer[Measure Name]) = "Sales Actual",[Sales Actual],

SELECTEDVALUE(Slicer[Measure Namae])="Sales Previous Year",[Sales Previous Year],

SELECTEDVALUE(Slicer[Measure Namae])="Sales Budget",[Sales Budget],

BLANK())