cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
powerbiven Frequent Visitor
Frequent Visitor

Top 5 Manfacturers and Others And Default Products

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

4 REPLIES 4
v-danhe-msft Super Contributor
Super Contributor

Re: Top 5 Manfacturers and Others And Default Products

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:

1.PNG

2.PNG

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

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
powerbiven Frequent Visitor
Frequent Visitor

Re: Top 5 Manfacturers and Others And Default Products

@v-danhe-msft 

 

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

 

Thanks

venakt

v-danhe-msft Super Contributor
Super Contributor

Re: Top 5 Manfacturers and Others And Default Products

Hi @powerbiven ,

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

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
powerbiven Frequent Visitor
Frequent Visitor

Re: Top 5 Manfacturers and Others And Default Products

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:-

 

SalesByProduct.PNG

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 

Expected Output.PNG

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

 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 276 members 2,955 guests
Please welcome our newest community members: