RankX with Filters

[mrpowrbihelp]

Hi,

I have a RankX formula that almost works.

I have a visual with a table that has each model we have but it needs to filter based on the account picked or the status of model ect.

This visual has multiple description fields for example, the intro date of the model the msrp ect and then measures with sales.

The current Rankx formula i'm using is:
Calculate(Rankx(All("Model column"),"Sales Measure"),AllSelected("Description fields"),AllSelected("Description fields"),AllSelected("Description fields"),AllSelected("Description fields"),AllSelected("Description fields"),AllSelected("Description fields"),AllSelected("Description fields"),AllSelected("Description fields"))

It almost gives me the result i am looking for. The table "model" is coming from is the Master product table and it has about 10,000 distinct models. So this rank formula says my lowest rank is 10,000, but i want it to be say 250 (the amount of models showing in the visual after the filters are applied)

How can i adjust the formula or it is possibly my relationships and i shouldn't be using the model column from the master product table?

Thank you!

[OwenAuger]

Hi @mrpowrbihelp 

It would be best to see the tables/relationships, along with which columns you are using in the visual.

Could you possibly post a pbix with santised data?

But based on your description so far, here is a possible measure structure, which I could be more confident in with some more details on the model.

=
VAR ModelsForRanking =
    CALCULATETABLE (
        SUMMARIZE ( SalesTable, ModelTable[Model Column] ),
        ALLSELECTED ()
    )
RETURN
    RANKX (
        ModelsForRanking,
        CALCULATE ( "Sales Measure", ALLEXCEPT ( ModelTable, ModelTable[Model Column] ) )
    )

Kind regards,

Owen

[v-lili6-msft]

hi  @mrpowrbihelp 

You need to use ALLSELECTED instead of ALL in the rank measure

http://tinylizard.com/all-vs-allselected/

If you still have the problem, please share the sample pbix file and your expected output.

Regards,

Lin

[amitchandak]

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale