Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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!
Blog
Twitter
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!
Blog
Twitter
LinkedIn
Anonymous
Not applicable

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

 

Thank you

Mira

@Anonymous  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!
Blog
Twitter
LinkedIn

@OwenAuger 

 

It works perfect. Thanks a lot!

 

/depple

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.