Dynamic Ranking of Product in Hierarchy

[JayJay11]

Hello all,

this is the first I am completely stuck with a problem in DAX. My problem is supposedly simple, but I believe the RANKX function is very hard to apply in real-world problems.

Desired end-result

I simply want to rank 3 products in a geographical hierarchy, considering also the slicer context of the report page (geography and a date range).

Both rows and columns are based on fields. Ideally, the table has just one measure.

The ranking shall be based on the latest date selected on the sheet.

This is the fact table

This is the geography table

The date table is just a standard calender connected via the date field.

I understand from reading countless other posts on RANKX issues, that in the end I could generate mutiple variables in a measure and then check with ISFILTERED() the current hierarchy level. I guess the issue that I have, is how to calculate the ranking on an aggregated level, like here Subregion?

Can someone provide a solution pattern for this?

Many thanks in advance!

[v-zhangti]

Hi, @JayJay11 

You can try the following methods.

New column:

Rank =
RANKX (
    FILTER (
        'Fact',
        [Country_ISO] = EARLIER ( 'Fact'[Country_ISO] )
            && [Date] = EARLIER ( 'Fact'[Date] )
    ),
    [Value],
    ,
    DESC
)

 

Is this the output you expect?

Best Regards,

Charlotte Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JayJay11]

Dear Charlotte,

many thanks for this suggestion. I understand, this solution uses a calculated column. We however need the ranking to be dynamic and change with the filter context. As such, a measure would be needed.

Do you have a solution for a measure?

Thank you!

[v-zhangti · Accepted solution]

Hi, @JayJay11 

Measure:

Rank Measure =
RANKX (
    FILTER (
        ALL ( 'Fact' ),
        [Country_ISO] = MAX ( 'Fact'[Country_ISO] )
            && [Date] = MAX ( 'Fact'[Date] )
    ),
    CALCULATE ( SUM ( 'Fact'[Value] ) ),
    ,
)

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JayJay11]

No one? Is this actually unsolvable with DAX?

[smpa01]

@JayJay11  provide sample data first

[JayJay11]

@smpa01 here is some sample data, does that suffice?

Product	ISO_Country	Date	Value
Product 1	CH	01.12.2021	7.153871417
Product 2	CH	01.12.2021	4.791841636
Product 3	CH	01.12.2021	8.671030065
Product 1	DE	01.12.2021	0.252021849
Product 2	DE	01.12.2021	7.39999411
Product 3	DE	01.12.2021	8.051734496
Product 1	NO	01.12.2021	7.503697651
Product 2	NO	01.12.2021	7.239210549
Product 3	NO	01.12.2021	1.123777987
Product 1	SW	01.12.2021	7.806173907
Product 2	SW	01.12.2021	3.857131311
Product 3	SW	01.12.2021	1.251952541
Product 1	CH	01.09.2021	4.609087907
Product 2	CH	01.09.2021	2.179643813
Product 3	CH	01.09.2021	9.386766622
Product 1	DE	01.09.2021	2.530506259
Product 2	DE	01.09.2021	8.964522569
Product 3	DE	01.09.2021	4.387980849
Product 1	NO	01.09.2021	0.810266905
Product 2	NO	01.09.2021	7.841674418
Product 3	NO	01.09.2021	1.120149594
Product 1	SW	01.09.2021	1.099291771
Product 2	SW	01.09.2021	4.258235797
Product 3	SW	01.09.2021	4.891234175

Country_ISO	Country	Subregion	Region
CH	Switzerland	Western Europe	Europe
DE	Germany	Western Europe	Europe
NO	Norway	Northern Europe	Europe
SW	Sweden	Northern Europe	Europe