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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!