Calcul of rank in the matrix for the hierarchies, for a range of dates

[yassine_mendy59]

Hi everyone,

This post follows a precedent one : https://community.fabric.microsoft.com/t5/Desktop/Calcul-of-rank-in-the-matrix-for-the-hierarchies/m...

This is the same situation but the difference is that I have now informations for differents dates among 2022,2023, 2024. This is a subset of the table : 

Date	Region	City	Department	Number of products solds
01/08/2023	Nord	Valenciennes	A	12 102
01/08/2023	Nord	Valenciennes	B	10 090
01/08/2023	Nord	Valenciennes	C	23 456
01/08/2023	Sud	Marseille	A	11 118
01/08/2023	Sud	Marseille	B	13 456
01/08/2023	Sud	Marseille	C	9850
02/08/2023	Nord	Valenciennes	A	6050
02/08/2023	Nord	Valenciennes	B	10 083
02/08/2023	Nord	Valenciennes	C	23 452
02/08/2023	Sud	Marseille	A	9453
02/08/2023	Sud	Marseille	B	8250
02/08/2023	Sud	Marseille	C	4321
...	...	...	...	...

Matrix with 3 levels of hierarchy

Region

              City

                            Department

The aim would be to have a different rank calculation for the matrix, depending on the hierarchy you're in:
- Region level :
Ranking between regions according to the number of products sold

- City level:
Ranking between cities according to the number of products sold per city

- Department level:
For the same department, I want the rank for the number of products sold for the same department between the different stores,
Ex -> for department A, I want between stores for department A.

I have three reports, to show this for different scales of date :

- Cumulative Year-To-Date

- Monthly

- Date to Date

The solution who was given to me in my previous report, works at Region and City Hierarchy, but the rank calculation doesn't work at the Department hierarchy level, I have some very strange ranks, who mean nothing.

Thank you for your help

[Anonymous · Accepted solution]

Hi, @yassine_mendy59 

Thanks for qqqqqwwwweeerrr reply. You can try the following dax to achieve your need.

rank group by department = 
RANKX (
    FILTER (
        'Table',
        'Table'[City] = EARLIER ( 'Table'[City] )
            && 'Table'[Region] = EARLIER ( 'Table'[Region] )
    ),
    CALCULATE (
        SUM ( 'Table'[Number of products solds] ),
        FILTER (
            'Table',
            'Table'[Region] = EARLIER ( 'Table'[Region] )
                && 'Table'[City] = EARLIER ( 'Table'[City] )
                && 'Table'[Department] = EARLIER ( 'Table'[Department] )
        )
    ),
    ,
    DESC,
    DENSE
)



Sort = 
VAR _currentDepartment =
    SELECTEDVALUE ( 'Table'[Department] )
VAR _currentCity =
    SELECTEDVALUE ( 'Table'[City] )
VAR _currentRegion =
    SELECTEDVALUE ( 'Table'[Region] )
VAR _currentDate =
    SELECTEDVALUE ( 'Table'[Date] )
VAR _vtable =
    ADDCOLUMNS (
        SUMMARIZE (
            ALLSELECTED ( 'Table' ),
            'Table'[Region],
            'Table'[City],
            'Table'[Department],
            'Table'[Date],
            "_SUM", [Sum]
        ),
        "_Region__Sort",
            RANKX (
                ALLSELECTED ( 'Table' ),
                CALCULATE (
                    [Sum],
                    FILTER (
                        ALLSELECTED ( 'Table' ),
                        'Table'[Region] = EARLIER ( 'Table'[Region] )
                    )
                ),
                ,
                DESC,
                DENSE
            ),
        "_City__Sort",
            RANKX (
                FILTER (
                    'Table',
                    'Table'[Region] = EARLIER ( 'Table'[Region] )
                        && 'Table'[City] = EARLIER ( 'Table'[City] )
                ),
                CALCULATE (
                    SUM ( 'Table'[Number of products solds] ),
                    FILTER (
                        'Table',
                        'Table'[Region] = EARLIER ( 'Table'[Region] )
                            && 'Table'[City] = EARLIER ( 'Table'[City] )
                            && 'Table'[Department] = EARLIER ( 'Table'[Department] )
                    )
                ),
                ,
                DESC,
                DENSE
            )
    )
RETURN
    IF (
        HASONEVALUE ( 'Table'[Date] ),
        RANKX (
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[Department] = _currentDepartment
                    && 'Table'[City] = _currentCity
                    && 'Table'[Region] = _currentRegion
            ),
            [Sum]
        ),
        IF (
            ISINSCOPE ( 'Table'[Department] ),
            MAXX (
                FILTER ( _vtable, [City] = _currentCity && 'Table'[Region] = _currentRegion ),
                DIVIDE (
                    SUM ( 'Table'[rank group by department] ),
                    CALCULATE (
                        COUNTROWS ( 'Table' ),
                        FILTER (
                            'Table',
                            'Table'[Region] = EARLIER ( 'Table'[Region] )
                                && 'Table'[City] = EARLIER ( 'Table'[City] )
                                && 'Table'[Department] = EARLIER ( 'Table'[Department] )
                        )
                    )
                )
            ),
            MAXX ( FILTER ( _vtable, [Region] = _currentRegion ), [_Region__Sort] )
        )
    )

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[ThxAlot · Accepted solution]

[Anonymous · Accepted solution]

Hi, @yassine_mendy59 

Thanks for qqqqqwwwweeerrr reply. You can try the following dax to achieve your need.

rank group by department = 
RANKX (
    FILTER (
        'Table',
        'Table'[City] = EARLIER ( 'Table'[City] )
            && 'Table'[Region] = EARLIER ( 'Table'[Region] )
    ),
    CALCULATE (
        SUM ( 'Table'[Number of products solds] ),
        FILTER (
            'Table',
            'Table'[Region] = EARLIER ( 'Table'[Region] )
                && 'Table'[City] = EARLIER ( 'Table'[City] )
                && 'Table'[Department] = EARLIER ( 'Table'[Department] )
        )
    ),
    ,
    DESC,
    DENSE
)



Sort = 
VAR _currentDepartment =
    SELECTEDVALUE ( 'Table'[Department] )
VAR _currentCity =
    SELECTEDVALUE ( 'Table'[City] )
VAR _currentRegion =
    SELECTEDVALUE ( 'Table'[Region] )
VAR _currentDate =
    SELECTEDVALUE ( 'Table'[Date] )
VAR _vtable =
    ADDCOLUMNS (
        SUMMARIZE (
            ALLSELECTED ( 'Table' ),
            'Table'[Region],
            'Table'[City],
            'Table'[Department],
            'Table'[Date],
            "_SUM", [Sum]
        ),
        "_Region__Sort",
            RANKX (
                ALLSELECTED ( 'Table' ),
                CALCULATE (
                    [Sum],
                    FILTER (
                        ALLSELECTED ( 'Table' ),
                        'Table'[Region] = EARLIER ( 'Table'[Region] )
                    )
                ),
                ,
                DESC,
                DENSE
            ),
        "_City__Sort",
            RANKX (
                FILTER (
                    'Table',
                    'Table'[Region] = EARLIER ( 'Table'[Region] )
                        && 'Table'[City] = EARLIER ( 'Table'[City] )
                ),
                CALCULATE (
                    SUM ( 'Table'[Number of products solds] ),
                    FILTER (
                        'Table',
                        'Table'[Region] = EARLIER ( 'Table'[Region] )
                            && 'Table'[City] = EARLIER ( 'Table'[City] )
                            && 'Table'[Department] = EARLIER ( 'Table'[Department] )
                    )
                ),
                ,
                DESC,
                DENSE
            )
    )
RETURN
    IF (
        HASONEVALUE ( 'Table'[Date] ),
        RANKX (
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[Department] = _currentDepartment
                    && 'Table'[City] = _currentCity
                    && 'Table'[Region] = _currentRegion
            ),
            [Sum]
        ),
        IF (
            ISINSCOPE ( 'Table'[Department] ),
            MAXX (
                FILTER ( _vtable, [City] = _currentCity && 'Table'[Region] = _currentRegion ),
                DIVIDE (
                    SUM ( 'Table'[rank group by department] ),
                    CALCULATE (
                        COUNTROWS ( 'Table' ),
                        FILTER (
                            'Table',
                            'Table'[Region] = EARLIER ( 'Table'[Region] )
                                && 'Table'[City] = EARLIER ( 'Table'[City] )
                                && 'Table'[Department] = EARLIER ( 'Table'[Department] )
                        )
                    )
                )
            ),
            MAXX ( FILTER ( _vtable, [Region] = _currentRegion ), [_Region__Sort] )
        )
    )

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[qqqqqwwwweeerrr]

Hi @yassine_mendy59 

This video might help you, please note this is not the exact solution but with some workaround, your problem can be addressed: https://youtu.be/cs2AwJljBrQ

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Check for more intersing solution here: www.youtube.com/@Howtosolveprobem

Regards

[yassine_mendy59]

It doesn't help so much, I am only doing what is in the video for some hierarchies, but it is for the alst one that it did'nt work, because the calculation is special.