Dynamic TopN Ranking using Parameters for Ribbon Chart

[AleksandraLel]

Hi All,

I have a Ribbon Chart displaying Volumes by Years and Products.

X-axis - Years (last 5 years)
Y-axis - Volumes
Legend - Products

The issue is the number of products – I have 3,500 different products, which limits their visibility. I want to give users the option to choose the Top Products, for example, Top 50, 100, 150, 200, etc. I’ve created a separate table with measures and a Total Volumes measure that works based on a parameter (allowing the user to select the number of Top Products to view in a slicer). I also have other filters, like Region, Customer Name, and Account Manager, so I’d like the chart to display the Top Products based on the selected parameter value while reflecting these other filters as well.

I managed to implement this solution on a standard table where I have the product name in the first column and the summed volume for each product in the second column. Here, everything works perfectly. The issue arises when I try to apply the measures and parameter to the ribbon chart. Since I have data in both the X-axis and Y-axis, the filtering doesn’t work correctly – the years on the X-axis are causing problems. When applying a parameter, such as Top 10, the chart shows far more than 10 products.

Do you have any suggestions to work around this? Ideally, I’d like the chart to display products with total volumes summed over the 5 years, ranked from highest to lowest. Is it even possible to achieve this with a ribbon chart?

Many thanks in advance for any tips!

[AleksandraLel · Accepted solution]

Dear @mark_endicott ,

Many Thanks for your reply. You can find the sample data here:

https://www.dropbox.com/scl/fi/eze5fyrox5oo4galvyha2/RibbonChart.pbix?rlkey=4zzxqcrqt2aomhvr0mtkygv8...

I have to mention also that the Top N Filter built based on the Parameter should work dynamicaly with changing other filters as Region, Sales Rep responsible for a given Region, Customer Name etc.

Thanks!

[AleksandraLel · Accepted solution]

Dear @mark_endicott ,

Many Thanks for your reply. You can find the sample data here:

https://www.dropbox.com/scl/fi/eze5fyrox5oo4galvyha2/RibbonChart.pbix?rlkey=4zzxqcrqt2aomhvr0mtkygv8...

I have to mention also that the Top N Filter built based on the Parameter should work dynamicaly with changing other filters as Region, Sales Rep responsible for a given Region, Customer Name etc.

Thanks!

[mark_endicott · Accepted solution]

@AleksandraLel - I've added a new measure to your file, which will point you in the right direction. To factor in the other dimension filters, you will just need to add them into the last ALLSELECTED separated by commas. 

DAX below, file attached. 

Dynamic TopN Volume = 
VAR vol = [TotalVolume]
RETURN
    IF (
        CALCULATE (
            RANKX ( ALLSELECTED ( Sheet1[Product Name] ), [TotalVolume] ),
            ALLSELECTED ( Sheet1[Year] )
        ) <= TopNProducts[TopNProducts Value],
        vol
    )

If this works for you please accept it as the solution, so others with the same issue can find the resolution. 

[AleksandraLel]

Hi @mark_endicott ,

Sorry for my late reply and thank you for your help! It works fine, I have accepted the solution 🙂

Aleksandra

[mark_endicott]

@AleksandraLel - No problem, happy to help!

[mark_endicott]

@AleksandraLel - Can you provide your DAX and some sample data please?

This is unsolvable without.