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.

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.