Top 5 Variances DAX

[Anonymous]

Hey All, 

How would I create a DAX formula to take the top 5 variance over OR under a threshold amount of $12k? 

Any help would be much appreciated. 

[Greg_Deckler]

Sample data and expected results would assist greatly.

[Anonymous]

@Greg_Deckler Here is the Variance and country column. (Attached)

Expected Results would Top 5 Var over sale's plan by Country:

1.France -12,050

2. USA - (12,050)

3. Germany - 50,000

4. Sweden - 75,000

5. Canada - (100,000)

[Anonymous]

Can anyone give some insight on this topic?

[v-shex-msft · Accepted solution]

HI @Anonymous,

You can try to use following measure if it works for your scenario.

Rank =
RANKX (
    ALL ( Table ),
    CALCULATE ( SUM ( Table[Variances] ), ALLEXCEPT ( Table, Table[Country] ) ),
    ,
    ASC,
    DENSE
)

If not help, please share a table with some sample data to test.

Regards,

Xiaoxin Sheng