cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

How to select top 10 companies based on the company filter selection

Hi Friends, Urgent Requirement

 

I have to display top 10 comapnies (by Rank) by current Year Sales and as per the company selection.

 

RankCompanySalesYear

 

If selected company is in the Top 10 list the should display the Top 10 companies. If selected compnay is not in Top 10 list then also Selected compnay should display along with the Top 10 companies.

 

Please help 

 

Thanks in Advance

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: How to select top 10 companies based on the company filter selection

Here is a pattern you can replicate to get your desired result.  

 

1. First create a separate disconnected table with just the Customer/Company names for your slicer with something like this

JustProducts = DISTINCT('Product'[Product])
 
2. Make a measure like this, but with your measure, table, and column names instead.  In this case, it is using a disconnected slicer to pick a product, and passing that filter to the original Product column.  If the product is in the top 3, 3 results will be shown in the table/visual.  If not, 4 will show.
 

 

TopN and Selected =
VAR __selectedproducts =
    TREATAS ( VALUES ( JustProducts[Product] ), 'Product'[Product] )
VAR __top3products =
    TOPN ( 3, ALL ( 'Product'[Product] ), [Total Sales], DESC )
VAR __unioned =
    DISTINCT ( UNION ( __selectedproducts, __top3products ) )
RETURN
    CALCULATE ( [Total Sales], KEEPFILTERS ( __unioned ) )

 

You can try this approach on a "sandbox" file I posted about recently that is useful for troubleshooting.

https://community.powerbi.com/t5/Community-Blog/A-Self-Contained-quot-Sandbox-quot-PBIX-File-to-Prac...

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

 

 

View solution in original post

Highlighted
Community Support
Community Support

Re: How to select top 10 companies based on the company filter selection

Hi @BhavyaM ,

You could refer to @mahoneypat 's suggestions, or you also could refer to my sample for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

9 REPLIES 9
Highlighted
Community Champion
Community Champion

Re: How to select top 10 companies based on the company filter selection

Here is a pattern you can replicate to get your desired result.  

 

1. First create a separate disconnected table with just the Customer/Company names for your slicer with something like this

JustProducts = DISTINCT('Product'[Product])
 
2. Make a measure like this, but with your measure, table, and column names instead.  In this case, it is using a disconnected slicer to pick a product, and passing that filter to the original Product column.  If the product is in the top 3, 3 results will be shown in the table/visual.  If not, 4 will show.
 

 

TopN and Selected =
VAR __selectedproducts =
    TREATAS ( VALUES ( JustProducts[Product] ), 'Product'[Product] )
VAR __top3products =
    TOPN ( 3, ALL ( 'Product'[Product] ), [Total Sales], DESC )
VAR __unioned =
    DISTINCT ( UNION ( __selectedproducts, __top3products ) )
RETURN
    CALCULATE ( [Total Sales], KEEPFILTERS ( __unioned ) )

 

You can try this approach on a "sandbox" file I posted about recently that is useful for troubleshooting.

https://community.powerbi.com/t5/Community-Blog/A-Self-Contained-quot-Sandbox-quot-PBIX-File-to-Prac...

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

 

 

View solution in original post

Highlighted
Community Support
Community Support

Re: How to select top 10 companies based on the company filter selection

Hi @BhavyaM ,

You could refer to @mahoneypat 's suggestions, or you also could refer to my sample for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

Highlighted
Helper V
Helper V

Re: How to select top 10 companies based on the company filter selection

@mahoneypat @dax 

Thank you for your solution. Its working for me

 

Here,

 

for one question i didnt get solution i.e., i need to Grey out the selected company in the Bar chart.

Highlighted
Helper V
Helper V

Re: How to select top 10 companies based on the company filter selection

Can you please help. How to show the selected company in different colour in the Bar chart.

Highlighted
Community Support
Community Support

Re: How to select top 10 companies based on the company filter selection

Hi @BhavyaM , 

If you want to highlight the bar, you can't use legend field in chart, you could try below measure, then use this in Data Color conditonal formatting

Measure = if(VALUES(t1[Company])=SELECTEDVALUE(company[Company]), "#f7b44f","#156ced")

 You could refer to my sample for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Highlighted
Helper V
Helper V

Re: How to select top 10 companies based on the company filter selection

TopN and Selected =
VAR __selectedproducts =
    TREATAS ( VALUES ( JustProducts[Product] ), 'Product'[Product] )
VAR __top3products =
    TOPN ( 3, ALL ( 'Product'[Product] ), [Total Sales], DESC )
VAR __unioned =
    DISTINCT ( UNION ( __selectedproducts, __top3products ) )
RETURN
    CALCULATE ( [Total Sales], KEEPFILTERS ( __unioned ) )

I have used this query to my report. this is working and For data bar highlight i have used

Measure = if(VALUES(t1[Company])=SELECTEDVALUE(company[Company]), "#f7b44f","#156ced")

 

Both are working but data bar highlighy is working only if i select the company other wise i am getting error as "cant display this visual"

 

Please help

 

Thanks in advance

Highlighted
Helper V
Helper V

Re: How to select top 10 companies based on the company filter selection

@mahoneypat 

 

Can you pleasehelp me how to show the selected company in different colour

TopN and Selected =
VAR __selectedproducts =
    TREATAS ( VALUES ( JustProducts[Product] ), 'Product'[Product] )
VAR __top3products =
    TOPN ( 3, ALL ( 'Product'[Product] ), [Total Sales], DESC )
VAR __unioned =
    DISTINCT ( UNION ( __selectedproducts, __top3products ) )
RETURN
    CALCULATE ( [Total Sales], KEEPFILTERS ( __unioned ) )

I have used this query to my report

Measure = if(VALUES(t1[Company])=SELECTEDVALUE(company[Company]), "#f7b44f","#156ced")

 

Both are working but data bar highlighy is working only if i select the company other wise i am getting error as "cant display this visual"

 

Thanks in advance

 

Highlighted
Community Champion
Community Champion

Re: How to select top 10 companies based on the company filter selection

Sorry for missing your earlier message.  Just change VALUES() to SELECTEDVALUE(), or switch the columns in the current measure.  If the slicer column is in VALUES(), you will get an error when >1 selected (or non selected), as you are passing a table into the expression.

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

Highlighted
Helper V
Helper V

Re: How to select top 10 companies based on the company filter selection

Thanks it worked

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors