Dynamic Top N using measure and the highest node in a hierarchy

[sbatool]

Hi all,
This is a very dynamic use case in which we need to provide dynamic top n functionaility by a field parameter. There is also an uneven client hierarchy in a matrix visual (used Parent-child hierarchies – DAX Patterns to remove blanks from the uneven hierarchy).
The top n selection needs to filter the matrix visual based on the highest level in the hierarchy. Meaning, if Top 3 by KPI1 is selected, it should display 3 clients (on the highest level in hierarchy) + their children nodes (any number of children levels they may have) sorted in the matrix by KPI 1.  The rest of the KPIs in the matrix will display based on the selected KPI 1. The total for the KPIs would change based on the Top n selection. Here is the final required output.

Note: The highest level will have its own KPI values too. This means that, for example, the sub total will aggregate KPI 1 for Client 1 (inclusive), A, B, C, and D.
Also, we got the above working functionality-wise using the TOPN() dax. However, when checking the data, it is noticed that with the TOPN(), only the positive values are displayed for the KPIs, it removes the negative values from the matrix. This gives a distored view of the insight about the data. To fix this, tried using RANKX(), RANK() and the WINDOW() funtion but with partial success. As this is approaching the deadline to deliver, any immediate help would be highly appreciated.
Thanks

P.S. Below are the DAX measures that are tried already.
------------------------------------------------------------

Selected KPI =

VAR SelectedMetricOrder =

    SELECTEDVALUE ( 'Metric'[Metric Order] )

VAR Result =

    SWITCH (

        TRUE (),

        SelectedMetricOrder = 0, [Sum of KPI 1],

        SelectedMetricOrder = 1, [Sum of KPI 2],

        SelectedMetricOrder = 2, [Sum of KPI 3],

        SelectedMetricOrder = 3, [Sum of KPI 4],

        SelectedMetricOrder = 4, [Sum of KPI 5],

        BLANK ()

    )

RETURN

    Result

------------------------------------------------------------

KPI 1 =

// Top N table based on Level 1

VAR TopClient1 =  

    TOPN (

        [Top N Value],

        ALLSELECTED ( Client[Level1] ),

        CALCULATE (

            [Selected KPI],

            REMOVEFILTERS ( Client[Level2] ),

            REMOVEFILTERS ( Client[Level3] ),

            REMOVEFILTERS ( Client[Level4] )

        ), DESC

    )

// Top N table based on Level 2

VAR TopClient2 =

    TOPN (

        [Top N Value],

        ALLSELECTED ( Client[Level2] ),

        CALCULATE (

            [Selected KPI],

            REMOVEFILTERS ( Client[Level3] ),

            REMOVEFILTERS ( Client[Level4] )

        ), DESC

    )

// Top N table based on Level 3

VAR TopClient3 =

    TOPN (

        [Top N Value],

        ALLSELECTED ( Client[Level3] ),

        CALCULATE ( [Selected KPI], REMOVEFILTERS ( Client[Level4] ) ), DESC

    )

// Top N table based on Level 4

VAR TopClient4 =

    TOPN ( [Top N Value], ALLSELECTED ( Client[Level4] ), [Selected KPI], DESC )

// Check whether each of the levels are filtered

VAR IsFilteredClient2 =

    ISFILTERED ( Client[Level2] )

VAR IsFilteredClient3 =

    ISFILTERED ( Client[Level3] )

VAR IsFilteredClient4 =

    ISFILTERED ( Client[Level4] )

// Get the selected values of each of the levels

VAR SelectedClient1 =

    SELECTEDVALUE ( Client[Level1] )

VAR SelectedClient2 =

    SELECTEDVALUE ( Client[Level2] )

VAR SelectedClient3 =

    SELECTEDVALUE ( Client[Level3] )

VAR SelectedClient4 =

    SELECTEDVALUE ( Client[Level4] )

VAR SelectedClient =

    SELECTEDVALUE ( Client[Client] )

// Check the scope of each of the levels

VAR IsInScopeClient1 =

    ISINSCOPE ( Client[Level1] )

VAR IsInScopeClient2 =

    ISINSCOPE ( Client[Level2] )

VAR IsInScopeClient3 =

    ISINSCOPE ( Client[Level3] )

VAR IsInScopeClient4 =

    ISINSCOPE ( Client[Level4] )

// Check the scope for the grand total

VAR IsInScopeGrandTotal =

    CALCULATE ( [Sum of KPI 1], KEEPFILTERS ( TopClient1 ) )

// Calculate the values based on the above filters/context

VAR Val =

    IF (

        [Top N Value] = BLANK (),

        [Sum of KPI 1],

        SWITCH (

            TRUE (),

            // When it is the 1st level

            IsInScopeClient1, CALCULATE ( [Sum of KPI 1], KEEPFILTERS ( TopClient1 ) ),

            // When going to the 2nd level

            IsInScopeClient2, CALCULATE ( [Sum of KPI 1], KEEPFILTERS ( TopClient2 ) ),

            // When going to the 3rd level

            IsInScopeClient3, CALCULATE ( [Sum of KPI 1], KEEPFILTERS ( TopClient3 ) ),

            // When going to the 4th level

            IsInScopeClient4, CALCULATE ( [Sum of KPI 1], KEEPFILTERS ( TopClient4 ) ),

            // When the 1st level is expanded up to the 2nd level

            IsInScopeClient1 && IsFilteredClient2

                && NOT ( ISBLANK ( SelectedClient2 ) )

                    && SelectedClient1 IN TopClient1, CALCULATE ( [Sum of KPI 1], ALLEXCEPT ( Client, Client[Level1] ) ),

            // When the 1st level is expanded up to the 3rd level

            IsInScopeClient1 && IsFilteredClient2

                && NOT ( ISBLANK ( SelectedClient2 ) ) && IsFilteredClient3

                    && NOT ( ISBLANK ( SelectedClient3 ) )

                        && SelectedClient1 IN TopClient1,

                CALCULATE (

                    [Sum of KPI 1],

                    ALLEXCEPT ( Client, Client[Level1] ),

                    ALLEXCEPT ( Client, Client[Level2] )

                ),

            // When the 1st level is expanded up to the 4th level

            IsInScopeClient1 && IsFilteredClient2

                && NOT ( ISBLANK ( SelectedClient2 ) ) && IsFilteredClient3

                    && NOT ( ISBLANK ( SelectedClient3 ) ) && IsFilteredClient4

                        && NOT ( ISBLANK ( SelectedClient4 ) )

                            && SelectedClient1 IN TopClient1,

                CALCULATE (

                    [Sum of KPI 1],

                    ALLEXCEPT ( Client, Client[Level1] ),

                    ALLEXCEPT ( Client, Client[Level2] ),

                    ALLEXCEPT ( Client, Client[Level3] )

                ),

            IsInScopeGrandTotal

        )

    )

// Check whether the browsed node is less than or equal to the total number of nodes in the hierarchy

VAR ClientShowRow = [Client Browse Depth] <= [Client Row Depth]

// Display the values only if the above condition is true

VAR Result =

    IF ( ClientShowRow, Val )

RETURN

    Result

-------------------------------------------------------------
KPI 1 =

VAR Val =

    IF (

        [Top N Value] = BLANK (),

        [Sum of KPI 1],        

        IF ( NOT ( ISBLANK ( [Selected KPI] ) ),

            CALCULATE (

                [Sum of KPI 1],

                KEEPFILTERS (

                    WINDOW (

                        1, ABS, [Top N Value], ABS,

                        ADDCOLUMNS (

                            SUMMARIZE (

                                ALLSELECTED ( 'Client Performance' ),

                                Client[Level1],

                                Client[Level2],

                                Client[Level3],

                                Client[Level4],

                                Client[Client Code]

                            ),

                        "Metric", [Selected KPI]

                        ),

                        ORDERBY ( [Selected KPI], DESC ),

                        PARTITIONBY (

                            Client[Level4], Client[Level2],

                            Client[Level3], Client[Level1]

                        ),

                        MATCHBY ( Client[Client Code] )

                    )

                )

            )

        )

    )

// Check whether the browsed node is less than or equal to the total number of nodes in the hierarchy

VAR ClientShowRow = [Client Browse Depth] <= [Client Row Depth]

// Display the values only if the above condition is true

VAR Result =

    IF ( ClientShowRow, Val )

RETURN

    Result

[sbatool]

Hi @Anonymous I am unsure if I can share via onedrive. Would you know if there is a secure link through which I could upload the sample file? 
Secondly, I have found out that the TOPN() works fine in my case. The only observation for TOPN() is that it displays the positive values first and then the zeroes and then the blanks and then the negatives. 

To remove the blanks, I have modified the above KPI 1 calculation that uses TOPN() as follows. It works fine in the collapsed mode (of the matrix visual), however when expand the hierarchy, it shows random client[Level1] in addition to the top N client[Level1].

// Top N table based on Level 1

VAR TopClient1 =

TOPN (

        [Top N Value],

        FILTER (

            ALLSELECTED ( Client[Level1] ), NOT ( ISBLANK ( [Selected KPI] ) ) ),

        CALCULATE (

            [Selected KPI],

            REMOVEFILTERS ( Client[Level2] )

        ), DESC

    )

// Top N table based on Level 2

VAR TopClient2 =

TOPN (

        [Top N Value],

        FILTER (

            ALLSELECTED ( Client[Level2] ), NOT ( ISBLANK ( [Selected KPI] ) ) ),

            [Selected KPI]

        , DESC

    )
Any idea how to get the result same as the screenshot I have shared above? 

[Anonymous]

Hi @sbatool ,

Could you please provide a simplified .pbix file that includes a subset of the data, visual, and any other relevant elements where you are encountering issues? Additionally, please provide a clear explanation of the specific problem you are facing and the desired outcome you wish to achieve based on the provided data. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards