cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## summarize monthly ranking per category

Hello, i was having some difficulties to get the DAX formula for monthly rank.

i would like to summarize my table that shows only the top rank per each month with respect to region category as shown below table.

anyone could assist me to achieve this. Thanks.

 Month Region Cost Rank ​​​​​​​​​​​Jan CR 2153098 ​​​​​​​​​​​Jan ER 1832120 ​​​​​​​​​​​Jan WR 1419893 ​​​​​​​​​​Feb CR 2093887 ​​​​​​​​​​Feb ER 1254795 ​​​​​​​​​​Feb WR 2932801 ​​​​​​​​​Mar CR 3843352 ​​​​​​​​​Mar ER 2167873 ​​​​​​​​​Mar WR 4185748 ​​​​​​​​Apr CR 5245395 ​​​​​​​​Apr ER 3301028 ​​​​​​​​Apr WR 7426182

The result table, that i could put graph

 Month Region Cost Rank ​​​​​​​​​​​Jan CR 2153098 1 ​​​​​​​​​​Feb WR 2932801 1 ​​​​​​​​​Mar WR 4185748 1 ​​​​​​​​Apr WR 7426182 1
1 ACCEPTED SOLUTION
Community Support

Hi @ivandgreat ,

I created a sample pbix file(see the attachment), please check if that is what you want.

1. Create a measure as below

Rank =
VAR _selmonth =
SELECTEDVALUE ( 'Table'[Month] )
RETURN
RANKX (
FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Month] = _selmonth ),
CALCULATE ( SUM ( 'Table'[Cost] ) ),
,
DESC,
DENSE
)

2. Create a table visual and apply a visual-level filter with the condition (Rank is 1)

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Community Support

Hi @ivandgreat ,

I created a sample pbix file(see the attachment), please check if that is what you want.

1. Create a measure as below

Rank =
VAR _selmonth =
SELECTEDVALUE ( 'Table'[Month] )
RETURN
RANKX (
FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Month] = _selmonth ),
CALCULATE ( SUM ( 'Table'[Cost] ) ),
,
DESC,
DENSE
)

2. Create a table visual and apply a visual-level filter with the condition (Rank is 1)

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
New Member

Create a calculated column and use 'RANKX' with a filter using the earlier function ie 'table'[month]=earlier('table'[month]). This ensure cost ranking is grouped by month. Here's a rough sketch:

Rank =
RANKX(
FILTER(
'Rank',
'rank'[Month] = EARLIER('rank'[Month])
),
'rank'[Cost],
,
DESC,
Dense
)
Upto this point, you can use the cost column in a graph and add a chart level filter that only filters where your new rank column = 1. It will work perfectly.
If you need a table with only top ranks like you had shown above, you can create a new table that filters out where the rank column = 1. Here's how:
Top_Rank_Table = FILTER('Rank','Rank'[Rank]=1).
I hope this helps!
Helper II

i have an error using the EARLIER Formula, show parameter is not correct.

My month column is from a calculated column

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors