Calculate rows in a matrix and Sorting

[jasonjklim]

I created the Sales and Revenue sheet for my report. 
I figured out the rows calculation of matrix from this link. 

https://community.powerbi.com/t5/Desktop/calculate-difference-between-two-rows-in-a-matrix/m-p/82832...

So, my DAX is like this

Measuretest =

VAR result =

CALCULATE (

SUM ( Waterfall[Value] ),

Distinct(VALUES ( 'Table'[Category]))

)

VAR list =

SUMMARIZE ( ALLSELECTED ( Waterfall ), 'Table'[Category], "Result", result )

RETURN

IF (

result <> BLANK (),

result,

VAR WF =

CALCULATE ( SUM ( Waterfall[Value] ), 'Table'[Category] = "Waterfall" )

VAR Loss =

CALCULATE ( SUM ( Waterfall[Value] ), 'Table'[Category] = "Losses" )

VAR MG =

CALCULATE ( SUM ( Waterfall[Value] ), 'Table'[Category] = "IG" )

VAR concession =

CALCULATE ( SUM ( Waterfall[Value] ), 'Table'[Category] = "Concessions" )

VAR FY21 =

CALCULATE ( SUM ( Waterfall[Value] ), 'Table'[Category] = "FY21 Recurring Fee" )

VAR FY21TR =

CALCULATE ( SUM ( Waterfall[Value] ), 'Table'[Category] = "FY21 Total Revenue")

VAR ED =

CALCULATE ( SUM ( Waterfall[Value] ), 'Table'[Category] = "Event-driven")

VAR Distribution =

CALCULATE ( SUM ( Waterfall[Value] ), 'Table'[Category] = "Distribution")

VAR FY22_total_rev =

CALCULATE ( SUM ( Waterfall[Value] ), 'Table'[Category] = "FY22 Total Revenue")

RETURN

SWITCH (

SELECTEDVALUE ( 'Table'[Category] ),

"NNB", WF + Loss,

--"Total Market Growth", MG,

"Internal Growth", MG+concession,

"Organic Growth", WF + Loss+ MG + concession,

"Recurring Fee Growth", WF + Loss+ MG + concession,

"FY22 Recurring Fee", FY21 + WF + Loss+ MG + concession,

"Total Revenue Growth", WF + Loss+ MG + concession+ED+ Distribution,

"FY22 Total Revenue", WF + Loss+ MG + concession+ED+ Distribution +FY21TR,

BLANK ()

)

)

Yet, When I tried to sort them in the matrix table, it only sorts only alphabectically. 

For custom sorting, I created a new excel file like below and create its relationship + change my DAX to point this table. 
Yet, all my variables became 0, and not appear in the matrix. 

After I created the Measure, I want to sort them with custom order. 

Like belows,

Yet, PBI only allows me to sort by alphabetically of Category column.

Please help

[v-yanjiang-msft]

Hi @jasonjklim ，

I found your solution in another thread.

1. Create "Table" with Category and sort index

2. Add Sort Index filter in Calculate Command like below. After this, the matrix is working.

Measuretest =
VAR result =
    CALCULATE (
        SUM ( Waterfall[Value] ),
        VALUES ( 'Table'[Category] ),
        VALUES ( 'Table'[SortIndex] )
    )
VAR list =
    SUMMARIZE ( ALLSELECTED ( Waterfall ), 'Table'[Category], "Result", result )
RETURN
    IF (
        result <> BLANK (),
        result,
        VAR WF =
            CALCULATE (
                SUM ( Waterfall[Value] ),
                'Table'[Category] = "Waterfall",
                'Table'[SortIndex] = 2
            )
        VAR Loss =
            CALCULATE (
                SUM ( Waterfall[Value] ),
                'Table'[Category] = "Losses",
                'Table'[SortIndex] = 3
            )
        VAR MG =
            CALCULATE (
                SUM ( Waterfall[Value] ),
                'Table'[Category] = "IG",
                'Table'[SortIndex] = 5
            )
        VAR concession =
            CALCULATE (
                SUM ( Waterfall[Value] ),
                'Table'[Category] = "Concessions",
                'Table'[SortIndex] = 5
            )
        VAR FY21 =
            CALCULATE (
                SUM ( Waterfall[Value] ),
                'Table'[Category] = "FY21 Recurring Fee",
                'Table'[SortIndex] = 1
            )
        VAR revenue_growth =
            CALCULATE (
                SUM ( Waterfall[Value] ),
                'Table'[Category] = "Total Revenue Growth"
            )
        VAR FY21TR =
            CALCULATE (
                SUM ( Waterfall[Value] ),
                'Table'[Category] = "FY21 Total Revenue",
                'Table'[SortIndex] = 12
            )
        VAR rf =
            CALCULATE ( SUM ( Waterfall[Value] ), 'Table'[Category] = "Recurring Fee" )
        VAR ED =
            CALCULATE (
                SUM ( Waterfall[Value] ),
                'Table'[Category] = "Event-driven",
                'Table'[SortIndex] = 14
            )
        VAR Distribution =
            CALCULATE (
                SUM ( Waterfall[Value] ),
                'Table'[Category] = "Distribution",
                'Table'[SortIndex] = 15
            )
        VAR FY22_total_rev =
            CALCULATE ( SUM ( Waterfall[Value] ), 'Table'[Category] = "FY22 Total Revenue" )
        RETURN
            SWITCH (
                SELECTEDVALUE ( 'Table'[Category] ),
                "Net New Business", WF + Loss,
                "Market Driven", MG,
                "Internal Growth", MG + concession,
                "Organic Growth",
                    WF + Loss + MG + concession,
                "Recurring Fee Growth",
                    WF + Loss + MG + concession,
                "Recurring Fee",
                    WF + Loss + MG + concession,
                "FY22 Recurring Fee",
                    FY21 + WF + Loss + MG + concession,
                "Total Revenue Growth",
                    WF + Loss + MG + concession + ED + Distribution,
                "FY22 Total Revenue",
                    WF + Loss + MG + concession + ED + Distribution + FY21TR,
                " ", " ",
                BLANK ()
            )
    )

If you have no other questions, would you mind accept your method as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

Best Regards,
Community Support Team _ kalyj

[amitchandak]

@jasonjklim , You marked index as sort column for category and sorted on category in visual

How to Create Sort Column and Solve Related Errors:
https://www.youtube.com/watch?v=KK1zu4MBb-c