Dynamic RANKX with external filters

[webportal]

I have two tables:

Fact:

And 'People':

The tables are related by Person.

How to calculate the dynaming ranking of TotalSales measure:

TotalSales = SUM('Fact'[Sales])

So that the Ranking responds to the external slicers but not to the rows filter of the table:

The measure I'm trying is the obvious:

Ranking = RANKX(ALLSELECTED('Fact'),CALCULATE([TotalSales]))

And many other similar combinations that return wrong results.

[v-xicai]

Hi @webportal ,

You may create measure like DAX below.

Ranking =
RANKX (
    FILTER (
        ALLSELECTED ( Fact ),
        Fact[Place] = MAX ( Fact[Place] )
    ),
    CALCULATE ( SUM ( Fact[Sales] ) ),
    ,
    DESC,
    Skip
    
)

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[webportal]

@v-xicai @amitchandak 

I get all 1, this DAX is just bull**bleep**, isn't it?

I mean, 48 hours to create a ranking??? And still NO GO!?!

[amitchandak]

@webportal , all selected should have work. remove the person from person table and use it person id from fact and check

[webportal]

@amitchandak thanks, but all fields in the visual are from Fact table.