Make TopN measure excluding specific values to use to filter Matrix visual

[MichaelaMul]

Hi,

I was wondering if it was possible to make a TopN measure that gets the Top 3 brands by Vol Sales, with the inclusion of always a list of specific brands? Previously, I was using a filter on a Matrix that showed the Top 3 Brands by Vol Sales, however I realized one of our own brands isn't the Top Brand in the category at a specific account and I want our brands to always be visible in the matrix.

Here's the desired answer that I would then want to use as a filter for a matrix visual:

Name	Sum of Volume Sales	Desired Ranking (Top 3 Brands (2 and Our Brand))
Almond Creamer	28,375.30
Blueberry Creamer	38,826.75	2
Hazelnut Creamer	46,431.93	1
Our Creamer	14,328.25	3

Below is the sample data:

[v-achippa · Accepted solution]

Hi @MichaelaMul,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @kushanNa for the prompt response.

Yes the earlier logic works perfectly when Product is in rows. However power bi does not support applying visual level filters to column headers using measures like [TopN Plus Our Brand]. That is why when you put Brand/Product in columns, the filtering does not apply the same way.

Please follow the below steps:

• Create a new disconnected table that lists only the top 3 products and 'Our Creamer' using this following DAX:
  TopN Brands With Our =
  VAR _TopN = 3
  VAR _OurBrand = "Our Creamer"
  VAR _AllSales =
  SUMMARIZE(
  'YourTable',
  'YourTable'[Product],
  "TotalSales", [Total Volume Sales]
  )
  VAR _Top =
  TOPN(_TopN, _AllSales, [TotalSales], DESC)
  VAR _IncludeBrand =
  FILTER(_AllSales, 'YourTable'[Product] = _OurBrand)
  RETURN
  DISTINCT(UNION(_Top, _IncludeBrand))
  
  

• Use this new table in a slicer or as a filter to control which brands appear as columns in your matrix.

• Filter your main matrix visual so it only includes products that exist in this new table, this will restrict your matrix to show only the Top 3 products and ‘Our Creamer’, even when products are in the column headers.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thanks and regards,

Anjan Kumar Chippa

[v-achippa · Accepted solution]

Hi @MichaelaMul,

Yes, it is possible to group TopN rankings by categories like Geography or Segment. But the UNION approach in a calculated table will not work well because it does not support row context grouping. Instead using a DAX measure with RANKX is the better approach.

Here is a sample measure:

TopN by Geo and Segment =

VAR _TopN = 3

RETURN

IF (

    RANKX (

        FILTER (

            ALL('YourTable'),

            'YourTable'[Geography] = SELECTEDVALUE('YourTable'[Geography])

                && 'YourTable'[Segment] = SELECTEDVALUE('YourTable'[Segment])

        ),

        [Total Volume Sales],

        ,

        DESC

    ) <= _TopN

    || 'YourTable'[Product] = "Our Creamer",

    1,

    0

)

You can use this measure in your matrix visual as a visual level filter and set it to 1. This way your matrix will show the Top 3 brands per Geography/Segment and always include “Our Creamer”.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thanks and regards,

Anjan Kumar Chippa

[kushanNa]

Hi @MichaelaMul 

Follow the following steps

create following 3 mesures

Total Volume Sales :=
SUM('YourTableName'[Volume Sales])

TopN Plus Our Brand :=
VAR _TopN = 3
VAR _OurBrands = {"Our Creamer"}  -- Add more if needed
VAR _AllBrandSales =
    SUMMARIZE(
        ALL('YourTableName'[Product]),
        'YourTableName'[Product],
        "Sales", [Total Volume Sales]
    )
VAR _TopBrands =
    TOPN(_TopN, _AllBrandSales, [Sales], DESC)
VAR _AlwaysInclude =
    FILTER(
        _AllBrandSales,
        'YourTableName'[Product] IN _OurBrands
    )
VAR _FinalTable =
    UNION(_TopBrands, _AlwaysInclude)
RETURN
IF(
    MAX('YourTableName'[Product]) IN SELECTCOLUMNS(_FinalTable, "Product", [Product]),
    1,
    0
)

Desired Ranking :=
RANKX(
    FILTER(
        ALL('YourTableName'[Product]),
        [TopN Plus Our Brand] = 1
    ),
    [Total Volume Sales],
    ,
    DESC,
    DENSE
)

 

1. Place Product in rows.

2. Add Total Volume Sales and Desired Ranking as values.

3. Drag TopN Plus Our Brand into Visual filters, and set it to “is 1”.

[MichaelaMul]

Thank you for your quick reply! Tried this and it worked in that instance above. Is it possible to have it so I can use those measures to filter the matrix table highlighted below. I would like to have a matrix with the time in the rows, brand in the columns, and then the vol sales and vol sales YA as values. But I would like to only see the Products that are ranked in the Top 3 (by the Desired Ranking you made). Is there a way to do that? So for example, I would like the matrix table circled in pink, but only show the highlighted brands

[v-achippa · Accepted solution]

Hi @MichaelaMul,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @kushanNa for the prompt response.

Yes the earlier logic works perfectly when Product is in rows. However power bi does not support applying visual level filters to column headers using measures like [TopN Plus Our Brand]. That is why when you put Brand/Product in columns, the filtering does not apply the same way.

Please follow the below steps:

• Create a new disconnected table that lists only the top 3 products and 'Our Creamer' using this following DAX:
  TopN Brands With Our =
  VAR _TopN = 3
  VAR _OurBrand = "Our Creamer"
  VAR _AllSales =
  SUMMARIZE(
  'YourTable',
  'YourTable'[Product],
  "TotalSales", [Total Volume Sales]
  )
  VAR _Top =
  TOPN(_TopN, _AllSales, [TotalSales], DESC)
  VAR _IncludeBrand =
  FILTER(_AllSales, 'YourTable'[Product] = _OurBrand)
  RETURN
  DISTINCT(UNION(_Top, _IncludeBrand))
  
  

• Use this new table in a slicer or as a filter to control which brands appear as columns in your matrix.

• Filter your main matrix visual so it only includes products that exist in this new table, this will restrict your matrix to show only the Top 3 products and ‘Our Creamer’, even when products are in the column headers.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thanks and regards,

Anjan Kumar Chippa

[MichaelaMul]

Do you have any ideas if I need to group the Rankings by Geography or Categories. For example, if there were more than 1 grocery store and more than just that segment group. Could I still use the TopN formula or would a ranking formula be better? I tried to edit the table you gave above but the Union wouldn't work.

[v-achippa · Accepted solution]

Hi @MichaelaMul,

Yes, it is possible to group TopN rankings by categories like Geography or Segment. But the UNION approach in a calculated table will not work well because it does not support row context grouping. Instead using a DAX measure with RANKX is the better approach.

Here is a sample measure:

TopN by Geo and Segment =

VAR _TopN = 3

RETURN

IF (

    RANKX (

        FILTER (

            ALL('YourTable'),

            'YourTable'[Geography] = SELECTEDVALUE('YourTable'[Geography])

                && 'YourTable'[Segment] = SELECTEDVALUE('YourTable'[Segment])

        ),

        [Total Volume Sales],

        ,

        DESC

    ) <= _TopN

    || 'YourTable'[Product] = "Our Creamer",

    1,

    0

)

You can use this measure in your matrix visual as a visual level filter and set it to 1. This way your matrix will show the Top 3 brands per Geography/Segment and always include “Our Creamer”.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @MichaelaMul,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution I have provided for the issue worked? or let us know if you need any further assistance.
If my response addressed, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @MichaelaMul,

We wanted to kindly follow up to check if the solution I have provided for the issue worked.
If my response addressed, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @MichaelaMul,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution I have provided for the issue worked.
If my response addressed, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[MichaelaMul · Accepted solution]

Hi- Yes what I ended up doing was making 2 calculated columns- one to indicate if it was our brands and then another one to rank them by Volume Sales

OurBrand = 
if(('Category'[Custom Brand Value (groups)]) = "Our Brand", 1,0)

Sales Column rank = IF('Category'[OurBrand] = 0, (RANKX(FILTER('Category','Category'[Geography] = EARLIER('Category'[Geography]) && 'Category'[Concat (groups)] = EARLIER('Category'[Concat (groups)])), 'Category'[TotalSales])), 0)

[MichaelaMul]

Geography	Product	Time	Volume Sales	Volume Sales Year Ago	Segment Group
Grocery Store	Almond Creamer	Week Ending 04-27-25	6,173	4,930	Coffee Creamer
Grocery Store	Almond Creamer	Week Ending 04-20-25	7,435	5,608	Coffee Creamer
Grocery Store	Almond Creamer	Week Ending 04-13-25	7,402	5,261	Coffee Creamer
Grocery Store	Almond Creamer	Week Ending 04-06-25	7,365	4,598	Coffee Creamer
Grocery Store	Almond Creamer	Week Ending 05-25-25		4,430	Coffee Creamer
Grocery Store	Almond Creamer	Week Ending 05-18-25		4,426	Coffee Creamer
Grocery Store	Almond Creamer	Week Ending 05-11-25		4,668	Coffee Creamer
Grocery Store	Almond Creamer	Week Ending 05-04-25		4,528	Coffee Creamer
Grocery Store	Blueberry Creamer	Week Ending 04-27-25	8,060	12,094	Coffee Creamer
Grocery Store	Blueberry Creamer	Week Ending 04-20-25	9,810	13,219	Coffee Creamer
Grocery Store	Blueberry Creamer	Week Ending 04-13-25	10,282	12,531	Coffee Creamer
Grocery Store	Blueberry Creamer	Week Ending 04-06-25	10,674	11,185	Coffee Creamer
Grocery Store	Blueberry Creamer	Week Ending 05-25-25		13,662	Coffee Creamer
Grocery Store	Blueberry Creamer	Week Ending 05-18-25		13,211	Coffee Creamer
Grocery Store	Blueberry Creamer	Week Ending 05-11-25		12,777	Coffee Creamer
Grocery Store	Blueberry Creamer	Week Ending 05-04-25		11,588	Coffee Creamer
Grocery Store	Hazelnut Creamer	Week Ending 04-27-25	9,892	9,056	Coffee Creamer
Grocery Store	Hazelnut Creamer	Week Ending 04-20-25	12,701	10,090	Coffee Creamer
Grocery Store	Hazelnut Creamer	Week Ending 04-13-25	12,930	10,229	Coffee Creamer
Grocery Store	Hazelnut Creamer	Week Ending 04-06-25	10,909	10,983	Coffee Creamer
Grocery Store	Hazelnut Creamer	Week Ending 05-25-25		8,840	Coffee Creamer
Grocery Store	Hazelnut Creamer	Week Ending 05-18-25		8,827	Coffee Creamer
Grocery Store	Hazelnut Creamer	Week Ending 05-11-25		8,848	Coffee Creamer
Grocery Store	Hazelnut Creamer	Week Ending 05-04-25		8,254	Coffee Creamer
Grocery Store	Our Creamer	Week Ending 04-27-25		7,151	Coffee Creamer
Grocery Store	Our Creamer	Week Ending 04-20-25	5,019	5,008	Coffee Creamer
Grocery Store	Our Creamer	Week Ending 04-13-25	4,614	6,034	Coffee Creamer
Grocery Store	Our Creamer	Week Ending 04-06-25	4,696	8,038	Coffee Creamer
Grocery Store	Our Creamer	Week Ending 05-25-25		5,151	Coffee Creamer
Grocery Store	Our Creamer	Week Ending 05-18-25		4,828	Coffee Creamer
Grocery Store	Our Creamer	Week Ending 05-11-25		6,119	Coffee Creamer
Grocery Store	Our Creamer	Week Ending 05-04-25		8,466	Coffee Creamer