- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
DAX TOP N Measure on a Matrix Issue
Hello,
I have a requirement to display TOP 10 States on a Volume measure across a matrix visual. I have used the TOP N DAX Measure to achieve this :
CALCULATE([Volume], TOPN(10, ALL(dimGeography[State_Code]), [Volume], DESC), VALUES(dimGeography[State_Code]))
Now if I use this measure on a matrix with just the State field on the Columns it works fine by displaying the TOP 10 states:
But if I were to include some field on the rows of the matrix as well, its doesn't display the true TOP 10 states but TOP 10 for each item on the row. The bigger problem is that the Total appears only for the True TOP 10 states and not others:
I think this is correct because when you include a field for the row, it considers the row context and pulls TOP 10 for that row. And the Totals are showing up only on the actual true TOP 10 states and not others. But is there a way to tweak the measure or visual to just display the True TOP 10 states only when including a row field as well? I am guessing that it would involve eliminating the row context on the measure.
I have also tried using a variation using RANKX to acheive this TOP 10 measure but its the same result.
CALCULATE([Volume], FILTER(VALUES(dimGeography[State_Code]),IF(RANKX(ALL(dimGeography[State_Code]),[Volume],,DESC)<=10,[Net Revenue], BLANK())))
Would appreciate any help on this. Please let me know if you need more information!
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@Koushikrish glad it worked!
To also remove the filter from Brand, this below might work (I've not tested this)
CALCULATE(
[Volume],
TOPN(10,
ALL(dimGeography[State_Code], dimGeography[RegionName]),
CALCULATE([Volume], ALL('dimBrand'[BrandName]))
DESC
),
VALUES(dimGeography[State_Code])
)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@Koushikrish , how should the visual look when you have Region added? Could you post the desired output, please.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello @Anonymous. These are the top 10 states. My desired output is to retain these Top 10 states only across columns even if I include anything on the rows.
Top 10 states:
This is how I need it when I include Region Name on the columns :
But right now it shows this way when I include Region Name, its displaying the Top 10 states for each Region.
Hope that info was sufficient. Else please let me know.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@Koushikrish I tried with some sample data but couldn't reproduce the first image in your firdt post.
I think that perhaps that when the Region field is added, you need the measure remove the filters from the Region Field. I think this will do it:
CALCULATE(
[Volume],
TOPN(10,
ALL(dimGeography[State_Code], dimGeography[RegionName]),
[Volume],
DESC
),
VALUES(dimGeography[State_Code])
)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@Anonymous : This worked great, thank you! I have another question though. Consider I have to include another column from another table on the Row (instead of Region Name). Is there a way to get rid of the filter without explicity specifiying the field name?
For example, I tried to use another column called Brand Family on the row but it won't work as filter is removed only on the Region Name. I also tried this but didn't work as it looks like arguments for ALL function must be from same table.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@Koushikrish glad it worked!
To also remove the filter from Brand, this below might work (I've not tested this)
CALCULATE(
[Volume],
TOPN(10,
ALL(dimGeography[State_Code], dimGeography[RegionName]),
CALCULATE([Volume], ALL('dimBrand'[BrandName]))
DESC
),
VALUES(dimGeography[State_Code])
)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@Anonymous : that worked great as well. Got the result as I desired for exactly! Thanks a lot for your help on this!! 😊
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@Koushikrish , try if this can help
measure =
var _tab = TOPN(10, ALL(dimGeography[State_Code]), [Volume], DESC)
return
CALCULATE([Volume], FILTER(VALUES(dimGeography[State_Code]),dimGeography[State_Code] in _tab))
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview. ✨
️ November 12th-14th, 2024
Online Event
Register Here
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @amitchandak , thanks for the response. This doesn't seem to fix the issue though. It doesn't appear to show just top 10 but all states and mapped to their corresponding region :
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
03-15-2024 02:42 AM | |||
06-12-2024 09:40 AM | |||
02-04-2024 03:24 AM | |||
12-21-2023 12:16 AM | |||
02-21-2024 12:56 AM |
User | Count |
---|---|
118 | |
73 | |
56 | |
56 | |
43 |
User | Count |
---|---|
181 | |
121 | |
82 | |
69 | |
54 |