Rankx - Top 5 values

Hi Ahsan_ ,

1. The reason you are getting the full total in the total row is because there is no concept of a manager filtering this value so there is nothing to rank. To remedy this you can create a new measure that iterates over the all the values of manager and adds them up.

Top 5 Managers Enhanced =
SUMX(
    VALUES( Store[Manager Name] ),
    [Top 5 managers]
)

2. When you add Store onto the axis that adds store to the filter context of the rank. All managers will be the top ranked for their store (assuming one manager per store) so they all show. I don't know the intricacies of the model but you could extend the above to state

Top 5 Managers Enhanced =
CALCULATE(
    SUMX(
        VALUES( Store[Manager Name] ),
        [Top 5 managers]
    ),
    ALL( Store[Store Name] )
)

I've assumed there that Store Name is in the same Store table as manager.

If you want it all in the same measure you could copy paste your original code in but make sure to wrap it in a CALCULATE so that context transition takes place

Top 5 Managers Enhanced =
CALCULATE(
    SUMX(
        VALUES( Store[Manager Name] ),
        CALCULATE(
            VAR ranking = RANKX(ALL(Store[Manager Name]),[Sales Commission],,DESC)
            RETURN
            IF(ranking <=5,[Sales Commission])
        )
    ),
    ALL( Store[Store Name] )
)

Hope it helps,

Kris