DAX Rank Formula

Question

I asked a similar question before of creating a DAX formula to sum rates for ranking purposes as seen by the 'Rank' column in the chart below (DAX Sum of Average to Rank). However, instead of summing rates I need to rank the calculated measure from highest to lowest over time.

Since the Goal is 80%, person F and H should be above person E and G. The ideal order would be A, B, C, D, F, H, E, G after sorting. This is in a pivot table so calculated columns cannot be created due to OLAP limitations, so DAX formulas are preferred. Any help is appreciated.

Answer 1

It is not clear what is your DAX to create the Rank. You may like to use something like below:

rate:=IF (
    HASONEVALUE ( DimDate[CalendarMonthName]),
    [Avg],
    CALCULATE ( SUMX (FactTable, [amount] ) )
)

Rank:=IF(NOT(ISBLANK([rate])),RANKX(ALLSELECTED(DimTable[bank]),
        [rate],,,Dense), BLANK())

The rate is similar to what @TJ_ provided in your other thread but it is a calculated field instead of column and it sums [amount] to be used in Rank otherwise it use your default average calculation.

You might not need ALLSELECTED. It is used to consider the values selected only by the slicers. Please see screen and post your DAX for the Rank if it is not helpful.

Thanks