RANKX Multiple Columns

[Omega]

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!

[v-zhangti · Accepted solution]

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]

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. 

[Ashish_Mathur]

Hi,

See if this link helps - Vendor Analysis In Inventory Management Dashboards.

[parry2k]

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

[parry2k]

@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]

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

[Omega]

Exactly. 

[smpa01]

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

[Omega]

Can't attach files 😞

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

Thanks!