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 country using a measure, can't use column as I'm connected to SSAS. I managed to create the RANKx measure measure but when filtering for TOP 20 at SKU level Power BI keeps processing endlessly. 

 

Problem: How to show the Top 20 SKUs per country using a measure and have Power BI presenet the data in few sec? 

 

 

Ranking = RANKX(ALL('Item'[Item]),[Difference],,DESC)

 

 

 

Thanks!

 

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.

9 Replies

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    Omega  is it kindly possible to create a sample pbix and share here?

    • Omega's avatar
      Omega
      Impactful Individual
      4 years ago

      Can't attach files 😞

       

      The data is similar to the screenshot with the assumption that we have millions of records. 

       

      Thanks!

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

    Omega how you are trying to get to the Top 20? Are you using the visual level filter on the Rank measure?

  • 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.⚡

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

    Omega and use this measure as a visual level filter where measure is not blank

     

     

  • v-zhangti's avatar
    v-zhangti
    Community Support
    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.

    • Omega's avatar
      Omega
      Impactful Individual
      4 years ago

      The measure will work but it's very slow to generate the results. If I had data for around a million records, it will take min to be executed or it might fail to show the results.