cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
depple
Helper III
Helper III

Top N Filter - Biggest changes

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

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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:image.png

     

Hopefully that helps. Please post back if needed.

 

Regards,

Owen


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

View solution in original post

4 REPLIES 4
OwenAuger
Super User
Super User

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:image.png

     

Hopefully that helps. Please post back if needed.

 

Regards,

Owen


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

View solution in original post

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

 

Thank you

Mira

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


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

@OwenAuger 

 

It works perfect. Thanks a lot!

 

/depple

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.