cancel
Showing results for
Did you mean:
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
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:

Hopefully that helps. Please post back if needed.

Regards,

Owen

Owen Auger

My Blog
4 REPLIES 4
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:

Hopefully that helps. Please post back if needed.

Regards,

Owen

Owen Auger

My Blog
Helper I

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

Thank you

Mira

Super User

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

Owen Auger

My Blog
Helper III

It works perfect. Thanks a lot!

/depple

Announcements

#### Launching new user group features

Learn how to create your own user groups today!