Multiple Visual Level Filters Question

[Gracie]

I have a table including columns such as Product Name, Days(it took to sell the product).  I want to make a bar chart. The bar chart shows the Top 10 fastest selling products. The Y-Axis is each product, X-Axis is average days it took to sell the product. I need to use a few filters to make the bar chart.

  1. The first is to filter the Days only from 0 to 365 days.

  2. The second is to filter the product units% of all products larget than 5%. (It means I only show the products which sold more than 5% of all products.)

      For this step, I created a measure and put it in the visual level filter. The measure is: 

        Units% per Product = DIVIDE(
                                                                   SUMX(
                                                                              KEEPFILTERS(VALUES('Industry'[Product])),
                                                                              CALCULATE(COUNT('Industry'[OrderID]))
                                                                               ),
                                                                    CALCULATE(COUNT('Industry'[OrderID]),ALL(Industry[Product])))

So far, it works fine.

  3. The third filter is to filter the top 10 products based on first two steps.  I have problem on this step. I tried to create a rankx measure and put in visual filter. But when filter rank less than 11. Nothing shows on the visual.

 And I don't want to use TOPN function to create a new table becuase I have a month slicer so that I can know the top 10 fastest selling product by each month.

Thanks for your help!

[CahabaData]

so I'm guessing the 3rd filter also affects the first 2 values/calculations ......

you could drop this down to being a calculated table and then build the visual off that new table and apply the RankX filter in a visual would work then as it doesn't affect the values of the first two columns of the table......

[Gracie]

Thank you for your reply. I understand your suggestion. But I have a month slicer so that when I choose each month, it will show different top 10 products. If I use the new table, I can't relate the new table with the data table and dynamicly show the result.

[v-huizhn-msft]

Hi @Gracie,

Please try to create a rank measure using the formula below, add the [Rank] measure as visual level filter, and check if it works fine.

Rank = RANKX(ALLSELECTED('Industry'),CALCULATE(SUM('Industry'[sell]),ALLEXCEPT('Industry','Industry'[Product],'Industry'[date])),,DESC,Dense)

You can review this knowledge base about ranking dynamically based on slicer.

If this still does not resolve your issue, do you mind share your sample table for further analysis?

Best Regards,
Angelia

[Gracie · Accepted solution]

Thank you for your reply!

I figured it out. I used the calculated column instead of the measure so that I can apply all the filters I need in the visual level filters, including the top N.