Top N Filter - Biggest changes

[depple]

Hi,

I would like to create a visual that shows the 10 products with the biggest changes in volume. As these can be both positive and negative, I tried to created an ABS-value measure for use in Visual level filter, but the Top N filter is (probably obviuosly) not available for the measure.

Any suggestions?

/depple

[OwenAuger · Accepted solution]

Hi @depple 

If you have a visual showing any measure by Product, you can certainly filter Product to the Top 10 based on absolute variance.

I created a simple example PBIX here and did the following:

1. Started with a Sales Amount Variance measure (in my example a year-on-year variance)
2. Created a SalesAmount Variance Absolute measure:
   

   Sales Amount Variance Absolute = 
   ABS ( [Sales Amount Variance] )

3. Created a visual showing Sales Amount Variance by Product, with a Top N filter on Product, set to Top 10 by Sales Amount Variance Absolute,
4. To illustrate that it's working correctly, created a visual showing Sales Amount Variance without the filters, with a Rank measure defining the colour (based on this article😞
   

   Rank of Product by Absolute Variance = 
   IF (
       ISINSCOPE( Sales[Product] ),
       RANKX (
           CALCULATETABLE (
               VALUES ( Sales[Product] ),
               ALLSELECTED ( Sales[Product] )
           ),
           [Sales Amount Variance Absolute]
       )
   )

5. Appears as follows:

Hopefully that helps. Please post back if needed.

Regards,

Owen

[Anonymous]

@OwenAuger  - the pbix file is not available on the link. Will it be possible to get it loaded again? 

Thank you

Mira

[OwenAuger]

@Anonymous  just edited my post and fixed the link - should be good to go now 🙂

[depple]

@OwenAuger 

It works perfect. Thanks a lot!

/depple