Make top N categories change based on selection

[DorienM]

I have a page where I am trying to visualize some data on training courses our company offers to our customers. I have two visuals I would like to include on this page that interact with eachother. The first is a clustered bar chart that uses course dates as the axis and the count of registrants for said course as the value. The second is a pie chart that will show the top 10 companies that have signed up for a training couse with us.

With all bars selected in the first visual the second visual gives me a good idea of which companies are most interested in our training material, however whenever I select a specifc course date by clicking on the bar for it in the first visual the second visual shows me the portion of registrants from the top 10 overall companies. Because couse content is different on each date I expect that different companies will be interested in different courses, so what I need this second visual to do is change what the top 10 companies are based off my selection in the first visual. How can I achieve this?

[amitchandak]

@DorienM , Refer if these can help

https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...

https://www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale

[mahoneypat]

Please post the DAX expression used in the measure in the visual on the right.  It may be as simple as changing the ALL() in that to an ALLSELECTED(), depending on how you achieved the top 10 effect.

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

Regards,

Pat

[DorienM]

So the first visual, the clustered bar uses 'Course Dates'[Name] as the axis and 'Count of Course Registrations' as the values which is a measure that looks like this:

Count of Course Registrations = COUNT('Course Registrations'[courseregistrationId])

The second visual, the top n pie chart uses 'Account'[Name] as the legend and 'Count of Course Registrations' as the values. There is a 1:Many relationship between 'Account' and 'Course Registrations' an another 1:Many relationship between 'Course Dates' and 'Course Registrations' using GUIDs from our CRM system.

The top n is achieved using a visual level filter.

[mahoneypat]

Please try this measure in your pie chart

Top 10 Count =
VAR top10accounts =
    TOPN ( 10, ALLSELECTED ( Account[Name] ), [Count of Course Registrations] )
RETURN
    CALCULATE ( [Count of Course Registrations], KEEPFILTERS ( top10accounts ) )

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

Regards,

Pat

[DorienM]

Hi Pat,

I used that measure as the value in the pie chart and unfortunately it doesn't seem to help. Selecting one or more bars on the clustered bar chart still only shows the proportion of registrations from the overall top 10 companies rather than changing the top 10 companies like I had hoped.