Ranking calculated column for group and subgroup for each week

[joshua1990]

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?

[Jihwan_Kim]

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 )

[joshua1990]

@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.

[Jihwan_Kim]

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.

[joshua1990]

@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.

[Jihwan_Kim · Accepted solution]

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 )
)

[Carl-J]

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

[amitchandak]

@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)