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
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!