DAX Dynamic Sort - PBI Report Builder

Hi diablo9081 ,

Thank you for the update. As you mentioned in your previous response,  you want to  perform a groupby and aggregate in the EVALUATE section like in the above DAX code to get the summary table to sort correctly.

Please try below two options.

1.  You need to compute SortValue inside the SUMMARIZECOLUMNS itself so that it’s aligned with the aggregated row values.

EVALUATE
TOPN (
10,
SUMMARIZECOLUMNS (
'Table'[Group_Code],
'Table'[Description],

"ARXCNT", SUM ( 'Table'[30_Day_Count] ),
"TotalCost", SUM ( 'Table'[Reimbursed_Amt] ),
"SortValue",
SWITCH (
TRUE(),
@SortBy = "Util", SUM ( 'Table'[30_Day_Count] ),
@SortBy = "Cost", SUM ( 'Table'[Reimbursed_Amt] )
)
),
[SortValue], DESC
)
ORDER BY [SortValue] DESC

2. If you want measure, keep the DEFINE MEASURE to rewrite it using SELECTCOLUMNS or ADDCOLUMNS after the aggregation.

DEFINE
MEASURE 'Table'[SortValue] =
SWITCH(
TRUE(),
@SortBy = "Util", SUM ( 'Table'[30_Day_Count] ),
@SortBy = "Cost", SUM ( 'Table'[Reimbursed_Amt] )
)

EVALUATE
TOPN (
10,
ADDCOLUMNS (
SUMMARIZECOLUMNS (
'Table'[Group_Code],
'Table'[Description]
),
"ARXCNT", SUM ( 'Table'[30_Day_Count] ),
"TotalCost", SUM ( 'Table'[Reimbursed_Amt] ),
"SortValue", [SortValue]
),
[SortValue], DESC
)
ORDER BY [SortValue] DESC

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh