CREATE HIDDEN [Total M] =
AGGREGATE(
DESCENDANTS(
AXIS( 1 ).ITEM( 0 ).DIMENSION.LEVELS( 0 ).ITEM( 0 ),
AXIS( 1 ).ITEM( 0 ).DIMENSION.LEVELS.COUNT
) - AXIS( 1 ).ITEM( 0 ).DIMENSION.LEVELS( 0 ).ITEM( 0 ),
[Measures].[M]
);
WITH CTE AS
(
SELECT
*,
ROW_NUMBER() OVER(PARTITION BY CategoryId ORDER BY ItemValue DESC) MaxRN,
ROW_NUMBER() OVER(PARTITION BY CategoryId ORDER BY ItemValue ASC) MinRN
FROM Items
)
SELECT
CategoryId,
MIN(CASE WHEN MaxRN = 1 THEN ItemId END) IdMaxValue,
MIN(CASE WHEN MinRN = 1 THEN ItemId END) IdMinValue
FROM CTE
GROUP BY CategoryId