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

[BhavyaM]

Hi Friends, Urgent Requirement

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

Rank

Company

Sales

Year

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

[mahoneypat · Accepted solution]

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

[dax · Accepted solution]

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.

[BhavyaM]

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

[mahoneypat · Accepted solution]

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

[BhavyaM]

@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

[mahoneypat]

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

[BhavyaM]

Thanks it worked

[BhavyaM]

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

[dax]

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.

[BhavyaM]

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