RankX in matrix with multiple hierarchy, multiple dimensions, and blanks

[MadsEmil]

Context: Handling blanks

In a previous post (here), I asked for help on how to handle rows with some blanks and some values in a matrix. Thanks to @_AAndrade I was able to solve my problem with the DAX below.

SoA_BlankHandle = 
    VAR _Tot = CALCULATE(SUM(Table1[Amount]), ALLSELECTED('Dates'))
RETURN
    SWITCH(
        TRUE(),

        _Tot = 0, BLANK(),

        ISBLANK(SUM(Table1[Amount])), 0,

        SUM(Table1[Amount])
    )

This hides rows with all blanks, and shows zeros instead of blanks in rows with one or more non-blank values.

The problem:

I am now attempting to create a ranking across all layers of the hierarchy (Group -> Subgroup -> Type). Ideally, this ranking should be the same across all years (identical to the column total). The behavior regarding the blanks is quite strange however. See the image below.

Three of the zeros in the matrix are blanks converted to zeros by SoA_BlankHandle. However these simply output 1's. The code for the Rnk_BlankHandle measure is:

Rnk_BlankHandle = 
    VAR _Group = MAX(Table1[Group])
    VAR _Subgroup = MAX(Table1[Subgroup])
RETURN
    SWITCH(
        TRUE(),

        ISINSCOPE(Table1[Type]),

        RANKX(
            FILTER(
                ALLSELECTED(Table1[Group],Table1[Subgroup],Table1[Type]),
                Table1[Group] = _Group
                && Table1[Subgroup] = _Subgroup
            ),
            CALCULATE(
                ABS([SoA_BlankHandle]),
                ALLSELECTED(Dates)
            )
        ),

        ISINSCOPE(Table1[Subgroup]),

        RANKX(
            FILTER(
                ALLSELECTED(Table1[Group],Table1[Subgroup]),
                Table1[Group] = _Group
            ),
            CALCULATE(
                ABS([SoA_BlankHandle]),
                ALLSELECTED(Dates)
            )
        ),

        ISINSCOPE(Table1[Group]),

        RANKX(
            ALLSELECTED(Table1[Group]),
            CALCULATE(
                ABS([SoA_BlankHandle]),
                ALLSELECTED(Dates)
            )
        ),

        BLANK()
    )

I suspect the reason for the behavior has to do with the lookup table generated in the first argument of the RankX-function. Despite my best efforts, I have been unable to figure out how to fix this behavior.

Summary:

I would like to have a ranking identical to the column total in each year, but it incorrectly assigns rank 1 to blank values.

Data:

Group	Subgroup	Type	Year	Amount
Group1	Subgroup1	Type1	2020	200
Group1	Subgroup1	Type2	2020	110
Group1	Subgroup2	Type3	2020	100
Group1	Subgroup2	Type4	2020	50
Group2	Subgroup1	Type1	2020	40
Group2	Subgroup1	Type2	2020	20
Group2	Subgroup2	Type3	2020	19
Group2	Subgroup2	Type4	2020	10
Group3	Subgroup1	Type2	2020	-2
Group3	Subgroup2	Type4	2020	3
Group4	Subgroup1	Type1	2020	-192
Group4	Subgroup1	Type2	2020	-104
Group4	Subgroup2	Type3	2020	-95
Group4	Subgroup2	Type4	2020	-48
Group5	Subgroup1	Type1	2020	-32
Group5	Subgroup1	Type2	2020	-14
Group5	Subgroup2	Type3	2020	-15
Group5	Subgroup2	Type4	2020	-10
Group1	Subgroup1	Type1	2021	209
Group1	Subgroup1	Type2	2021	92
Group1	Subgroup2	Type3	2021	87
Group1	Subgroup2	Type4	2021	60
Group2	Subgroup1	Type2	2021	22
Group2	Subgroup2	Type3	2021	21
Group2	Subgroup2	Type4	2021	12
Group3	Subgroup1	Type1	2021	1
Group3	Subgroup1	Type2	2021	-2
Group3	Subgroup2	Type3	2021	-1
Group3	Subgroup2	Type4	2021	4
Group4	Subgroup1	Type1	2021	-167
Group4	Subgroup1	Type2	2021	-91
Group4	Subgroup2	Type3	2021	-86
Group4	Subgroup2	Type4	2021	-57
Group5	Subgroup1	Type2	2021	-16
Group5	Subgroup2	Type3	2021	-16
Group5	Subgroup2	Type4	2021	-12

[MadsEmil]

My description of my problem has apparently been unclear. I will now provide some clarifying context. The following image is an example of the matrix with some added measures to communicate my problem more clearly.

I have a matrix with a three-level hierarchy on the rows, and a date variable (in my example, the years 2020 and 2021) on the columns. As values in the matrix is an amount, which can be zero, negative or positive. There is also the possibility that the value is blank. In my specific use case, a blank value is completely and unequivocally interpretable as the value being equal to zero. The yellow columns (the two left-most columns in each year) show the sum of this amount. The first of these insert a zero in place of blanks, while the second is included to illustrate where these blank values occur. The yellow columns work exactly as expected and wanted.

I have been instructed (and thus have no influence over whether it should be done this way) to rank the rows in the matrix by the absolute value of the sum of the row across all years. This value, the basis for the ranking, is provided in the red column, and is only included to illustrate functioning. It has been produced with the following dax:

Basis for rank = CALCULATE(ABS([SoA_BlankHandle]), ALLSELECTED(Dates))

The red column works exactly as expected and wanted.

The ranking is to be done by each combination of group and subgroup (top two hierarchy levels). I have been succesful in achieving this for most of the matrix. The only exception is the places where the blank values appear. I have underlined these with a thick red line in the image. The two blue columns have to do with this ranking. The first of these is my attempt at the ranking, dax code for this measure can be found in my original post. In the second blue column, I have replaced RANKX with CONCATENATEX. Specifically:

        CONCATENATEX(
            FILTER(
                ALLSELECTED(Table1[Group],Table1[Subgroup],Table1[Type]),
                Table1[Group] = _Group
                && Table1[Subgroup] = _Subgroup
            ),
            CALCULATE(
                ABS([SoA_BlankHandle]),
                ALLSELECTED(Dates)
            ), ", "
        ),

This is done to help troubleshoot any problems I might encounter when creating this ranking. This column shows the values in the lookup table created by the first argument of the RANKX function.

THE BLUE COLUMNS DO NOT FUNCTION AS EXPECTED OR WANTED, I AM LOOKING FOR ASSISTANCE IN REMEDYING THIS AND ONLY THIS.

In the places underlined with a thick red line in the image is where I have a problem. These are the places where a blank value appears in the matrix. In these places, the ranking measure always outputs the value 1, instead of correctly ranking according to the "Basis for rank" in the red column. Notice that the basis for rank is output normally even in the places with blank values. I suspect that the behavior of the ranking measure in the presence of these blank values has to do with the lookup table, as the second blue column is blank in these places, seeming to indicate that no lookup table is created.

I do not think I can make my question any clearer now. Please let me know if you can help me to understand this behavior of the RANKX function, and especially how to make it perform exactly as if the blank values are zero in this context.

[Anonymous]

Hi @MadsEmil 

According to your description, you want the ranking of the subgroup type to be consistent with the total ranking for each year. However, the total ranking may not necessarily be consistent with the ranking for individual years.

For example:

subgroup	2020	Rank	2021	Rank	Total	Rank
type1	100	1	10	2	110	2
type2	20	2	200	1	220	1

If you only focus on the ranking in the total column, I suggest you can choose to hide the rankings for individual years that you don't need.

For how to hide columns, you can refer to the following post.
Solved: Matrix - Hide a column in visual but consider in c... - Microsoft Fabric Community

Best Regards,

Jayleny

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

[MadsEmil]

It will not necessarily be consistent, but it is fairly easy to make it happen nonetheless, as apparent in the example matrix in my original post. The specifics of the ranking is not my problem, my problem is that it cannot correctly display any rank, not matter what it is, when the value of the matrix is blank.

[lbendlin]

identical to the column total in each year

which of the column totals?  Or do you mean rows (subgroup) ?  How would you rank blanks versus negative numbers?  Should you rank blanks?

[MadsEmil]

The column total shown in the picture. So the rankings should be equal to the rankings on the far right of the table. Identical ranking across all years. Let me know if this makes sense. This ranking should be based on the absolute value of the sum across all years. An example:

Group 5, subgroup 1 should be ranked so that Type1 has rank 1 in 2020, 2021 and in the total, and Type2 has rank 2 in 2020, 2021 and in the total. This is because the sum of Type1 values is -32+0=-32, and the sum of Type2 values is -14+(-16)=-30. Since ABS(-32)>ABS(-30), Type1 will be ranked highest. This already works as it can be seen in the image in my post.

Blanks should be ranked, and treated as zero when calculating the above sum used for the ranking. My problem is, that, every place a blank appears in the data, the ranking for that cell simply outputs 1.

I hope this cleared up any questions. Thank you 🙂

[lbendlin]

Blanks should be ranked, and treated as zero

I don't think I can agree with that, especially since you have negative values too.  I hope someone else can help you further.