topic Re: Ranking calculated column for group and subgroup for each week in DAX Commands and Tips

Ranking calculated column for group and subgroup for each week

joshua1990 — Thu, 24 Mar 2022 09:34:10 GMT

I have a table with this structure

Year-Week	Group	Subgroup	Value	Ranking (New)
2022-01	A	A1	1	2
2022-01	A	A2	1	2
2022-01	B	B1	5	1
2022-01	B	B2	5	1
2022-02	A	A1	5	1
2022-02	B	B1	3	2

Now I would like to add a column that shows me the rank for each week and for each group.

The result would be the last column.

How can this be done?

Re: Ranking calculated column for group and subgroup for each week

amitchandak — Thu, 24 Mar 2022 11:03:24 GMT

joshua1990 , Can explain logic , I tried a few combinations not able to get the same output

Column = rankx('Rank Table (2)',CALCULATE(sum([Value]), filter('Rank Table (2)', [Group] =EARLIER('Rank Table (2)'[Group]) && 'Rank Table (2)'[Subgroup] =max('Rank Table (2)'[Subgroup]) )),,DESC,Dense)

Re: Ranking calculated column for group and subgroup for each week

Jihwan_Kim — Thu, 24 Mar 2022 13:14:30 GMT

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new column.

Ranking CC = VAR currentweek = Data[Year-Week] VAR subgrouptable = SUMMARIZE ( FILTER ( Data, Data[Year-Week] = currentweek ), Data[Subgroup], Data[Value] ) RETURN RANKX ( subgrouptable, Data[Value],, DESC, DENSE )

Re: Ranking calculated column for group and subgroup for each week

joshua1990 — Fri, 25 Mar 2022 15:15:07 GMT

Jihwan_Kim Thanks a lot! That works perfectly well. Is there any chance to exclude all rows where [Value] = 0 ?

So the RANK stops when row has 0 as a value.

Re: Ranking calculated column for group and subgroup for each week

Jihwan_Kim — Fri, 25 Mar 2022 15:21:53 GMT

Hi,

Thank you for your feedback.

Could you please share the sample data, and then I can try to come up with more accurate solution.

Thanks.

Re: Ranking calculated column for group and subgroup for each week

joshua1990 — Sun, 27 Mar 2022 11:16:52 GMT

Jihwan_Kim THanks a lot!

Please find below sample data:

Year-Week	Group	Subgroup	Value	Ranking (New)
2022-01	A	A1	1	2
2022-01	A	A2	1	2
2022-01	B	B1	5	1
2022-01	B	B2	5	1
2022-02	A	A1	5	1
2022-02	B	B1	3	2
2022	B	B2	0

As you can see the last row has a 0 value. This should be excluded in the function.

Re: Ranking calculated column for group and subgroup for each week

Jihwan_Kim — Sun, 27 Mar 2022 12:58:38 GMT

Hi,

Please try the below for creating a new column.

Ranking CC = VAR currentweek = Data[Year-Week] VAR subgrouptable = SUMMARIZE ( FILTER ( Data, Data[Year-Week] = currentweek && Data[Value] <> 0 ), Data[Subgroup], Data[Value] ) RETURN IF( Data[Value] = 0, BLANK(), RANKX ( subgrouptable, Data[Value],, DESC, DENSE ) )

Re: Ranking calculated column for group and subgroup for each week

Carl-J — Thu, 02 Mar 2023 19:53:51 GMT

Thank you for that solution. And the version removing blanks is great.