Top 5 Manfacturers and Others And Default Products

[powerbiven]

Hi All,

In our project we have the following scenario to be solved.

As i can not post the project data here, i am replicating the scenario with adventureworksdw database.

Customer has one default product(for ex: Mountain-200 Black, 38), he is very interested in that product sales.

Now the requirement is, we should diplay Top 5 products based on sales and all others products sales should come 

in Others. if the default product(Mountain-200 Black, 38) is in TOP 5 product list then Top 5 and Others

if the default product not in Top 5 then Top 5 products, Default Product and all other products in others.

This should be displayed in Pie chart.

This should be filtered dynamically across all the other dimensions like DimDate,DimCountry, when we change the functionality also it should work.

Thanks..

[v-danhe-msft]

Hi @powerbiven ,

Based on my test, you could refer to below steps:

Create a measure(rank the top 5):

Rank 1 = COUNTROWS(FILTER(ALL('Table1'),ISONORAFTER('Table1'[Sales],SELECTEDVALUE(Table1[Sales]))))

Create a column to filter:

Rank 1 = COUNTROWS(FILTER(ALL('Table1'),ISONORAFTER('Table1'[Sales],SELECTEDVALUE(Table1[Sales]))))

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He

[powerbiven]

@v-danhe-msft 

Please consider the default product case also, this is not just displaying Top 5 and others.

Thanks

venakt

[v-danhe-msft]

Hi @powerbiven ,

what is your desired result? Could you please post your sample file and desired result if possible?

Regards,

Daniel He

[powerbiven]

Hi @v-danhe-msft 

Thanks for your Reply, please find the below explation.

i am replicating my scenario with adventure works DW, please find the sample data.

https://github.com/bodempudi/CodeSnippets/blob/master/SampleData.xlsx

Now the requirement is:-

From the above picture, we can see Sales by each Product.

I am considering "Mountain-200 Silver, 46" as default product.

We need to display top 5 products sales and the remaining products sales Under others category in Pie Chart.

if the default product(Mountain-200 Silver, 46) is in TOP5 then Top 5 and Others Category.

If the default product(Mountain-200 Silver, 46) is not in TOP5 then Top 5, Defulat Product, Others.

Please find the expected output 

we should be able to slice this by country and by year etc.

Means, When i select a coutry, result based on the country(the output should be dynamic based on slicer selection)

Thanks

Venkat