Forum Discussion

Omega's avatar
Omega
Impactful Individual
4 years ago
Solved

RANKX Multiple Columns

Hi,   I have two fact tables, Sales and Forecast, along with 2 dimension tables, Country and item.    I'm trying to show the top 20 SKUs without highest difference between sales and forecast per ...
Need Help
Show and Tell
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi, Omega 

     

    I simulated some data hoping to restore your problem.

    Measure:

    Difference = ABS(SUM(Sales[Sales])-SUM(Forecast[Forecast]))
    Ranking = RANKX(ALL(Country),[Difference],,DESC)

    Place the Ranking measure in filters on this visual, and after setting it as shown, click Apply Filter.

    If the method I provided above can't solve your problem, what's your expected result? Could you please provide more details for it?

     

    Best Regards,

    Community Support Team _Charlotte

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

parry2k's avatar
parry2k
Super User
4 years ago

Omega maybe create a measure using TOPN instead of using Rank

 

Top N = 
CALCULATE ( [Difference], KEEPFILTERS ( TOPN ( 20, ALL ('Items' ), [Difference], DESC ) ) )

 

and use this measure in the visual.

 

✨ Follow us on LinkedIn

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡