Filters pane doesn't work as expected

[anuragccsu]

I'm trying to find the top 5 selling products for each store in each location.

Initialliy i added, location, store and product and a [Total Sales] measure in the matrix visual and applied the TOPN filters from the filter pane using the [Total Sales] value. Please refer screenshot. However the output is not correct as i know by using excel pivot and checking the data. Though i'm able to find a solution using DAX but i want to know why the Filters pane filtering is not working as expected?

Can you help me?

[Anonymous · Accepted solution]

Hi, @anuragccsu 

You can populate the Product and Sales fields into the table visual object and then sort the fields in descending order based on the Sales field, where you can see that the Top 5 filtered fields in the matrix are the same as the top five fields in this table visual object. That's why Top N doesn't work.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[anuragccsu]

@community, Though i seem to solve it using DAX but i was really not able to comprehand the results of filter pane TOPN(5) filters as shared in my first comment of this topic. This was without DAX.

Filter -> Group -> Summarise is a general flow for any analytics query. Had that been followed here then the results would be same as DAX approach. I did a little research and found some information from MS Learn Power BI documentation:

https://learn.microsoft.com/en-us/training/modules/power-bi-effective-filters/2-report-structure

as it says

Unlike report and page-level filters, a visual-level filter can filter by using a measure. When a measure filters a visual, it's used to eliminate groups. Fo example, consider a column chart visual that groups by store. A measure filter could eliminate groups (stores) where the total store sales are less than a certain amount.

Very cryptic language and "Could". I think this can be suggested to Microsoft as enhancement.

Having said that all i'm still not able to comprehand the output by matix visual using the filter pane TOPN filter. For any curious reader, who'd like to explain how top5 were selected by the visual from all products at right to top5 in left:

Anyway i think after referring to MS Learn doc, i can sleep peacefully today 😀

[Anonymous]

Hi, @anuragccsu 

Thanks for elitesmitpatel and NordicDrow replies. Unfortunately, it didn't reproduce your problem, you can provide the pbix file without sensitive data for testing. Feel free to help you.

Best Regards,
Yang
Community Support Team

[anuragccsu]

@Anonymous, @elitesmitpatel, @NordicDrow please find the sample file. Refer the different workbooks for my experiment and one of them contains the solution using DAX.

The intent to share this file is to brainstorm and find a detailed root cause and working of the great BI product - Power BI. Help me enlighten with your knowledge and share your thoughts.

I can't upload files here as no access probably being a newbie to this community, however i've placed the file a g-drive link for you / anyone interested to debug / learn more about this problem:

https://drive.google.com/file/d/1gS6GBAaGXvGU4TJ35jQ6Eq1QWIkbWX2e/view?usp=sharing 

Please refer the post messages to understand the problem and revert in case of any clarification is required.

[elitesmitpatel]

Please share the Dummy PBIX file for easy reference.

[NordicDrow]

Hi Anurag,

Glad you were able to solve it. The problem, if I understand correctly, is that the filter will be done before the matrix groupping in the logic process.

So it will look for the top 5 categories that have the most sales overall, filter "Product" by those 5 values, and then pass that data to the matrix.

As you said, using a DAX formula for the top 5 filtering was the best solution. Could you share it? Might be useful for others in the future.

[anuragccsu]

The DAX i use is as follows:

Here [Total Sales] is a simple measure which sums the total sales of all the products. 'All' DAX function removes any filter from the 'Product' column. This expression works in case there are 2 products having same aggregated sales value and shows the list more than 5 products in those cases despite TOP5 being asked, which is i think good only! Refer next screenshot for such output: 

Top 5 Products by Sales = 
CALCULATE(
    Sales[Total Sales],
    TOPN ( 5, ALL ( Sales[Product] ), [Total Sales] ),
    VALUES(Sales[Product])
)