topic Re: summarize monthly ranking per category in DAX Commands and Tips

summarize monthly ranking per category

Anonymous — Fri, 29 Sep 2023 05:32:55 GMT

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

Re: summarize monthly ranking per category

Mageto — Fri, 29 Sep 2023 06:00:03 GMT

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!

Re: summarize monthly ranking per category

Anonymous — Fri, 29 Sep 2023 07:02:23 GMT

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

My month column is from a calculated column

Re: summarize monthly ranking per category

Anonymous — Thu, 05 Oct 2023 08:45:27 GMT

Hi Anonymous ,

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