Forum Discussion

anuragccsu's avatar
anuragccsu
Frequent Visitor
1 year ago
Solved

Filters pane doesn't work as expected

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 f...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

NordicDrow's avatar
NordicDrow
Helper I
1 year ago

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's avatar
    anuragccsu
    Frequent Visitor
    1 year ago

    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])
)