Top N products with their sales

[abisrani]

Hello Community,

I am looking for a solution for my report where I've got a table visual, a date slicer, and a slicer to select the quarter(Q1, Q2, Q3, Q4).

When I select a date range, my result should have the top 10 products with their sales.

The catch is I have another filter that has 4 quarters checked(selected) and I do not want to modify or change the products in the table visual when I select only one quarter.

Let us say I've selected Q1 and then the top 10 products should be the same but I am seeing the sales numbers for Q1 only.

Thank you.

[v-xinruzhu-msft · Accepted solution]

Hi @abisrani 

You can refer to the following solution.

Sample data 

1.Create a calendar table and create a relationship between tables.

2.Create quarter table

3.Create a measure

MEASURE =
VAR a =
    SUMMARIZE (
        TOPN ( 10, ALLSELECTED ( 'Sales' ), CALCULATE ( SUM ( 'Sales'[Value] ) ), DESC ),
        [Product]
    )
RETURN
    CALCULATE (
        SUM ( 'Sales'[Value] ),
        FILTER (
            'Sales',
            "Q"
                & QUARTER ( 'Sales'[Date] )
                    IN VALUES ( 'Quarter'[Quarter] )
                        && 'Sales'[Product] IN a
        )
    )

4.Then put the quarter of quarter table to a slicer and the measure to the table.

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Daniel29195]

@abisrani 

you can turn off the interaction between the slicer and the visual. 

let me know if this helps. 

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[abisrani]

Thanks for the suggestion. Actually, I want to see the changes for those applied filters. It is just that I want to retain the top the product list.
So example, if I am first seeing the top 10 in all the quarters sales and then selecting only 1st quarter, then I want to see the sales for the same products.

[lbendlin]

if I am first seeing the top 10 in all the quarters sales and then selecting only 1st quarter, then I want to see the sales for the same products.

The only way to accomplish that is to use a shadow copy of your dataset for the slicer and then use measures as visual filters.

[Ashish_Mathur]

Hi,

Your question is not clear.  Share some data to work with and show the expected result.

[abisrani]

Hello Ashish,
So the logic I am looking for is that when I change the date above, it should show me top 10 products and then when I change the Quarter from the Quarter slicer(say selecting Quarter 1), it should not affect the top 10 products and should show me the sales for the same 10 products in the Quarter 1.

Thank you.

[Ashish_Mathur]

Not sure of how much i can help but i'll try.  Share the download link of the PBI file.

[lbendlin]

You can get either or, but not both at the same time.  Teach your users how to use the filter pane advanced filtering (including Top N)  and allow them to personalize visuals.

You _could_  use disconnected tables to feed your slicers, and measures as visual filters.  But a robust data model is preferable.