Home Forums Power Pivot Show name of top ranking product

This topic contains 1 reply, has 2 voices, and was last updated by  tomallan 2 years, 10 months ago.

Viewing 2 posts - 1 through 2 (of 2 total)
• Author
Posts
• #5528

freekeys
Participant
• Started: 1
• Replies: 0
• Total: 1

Hello,

I’m hoping that someone can help – I’ve been trying to figure out 2 things in Powerpivot for ages!!

I have a table with sales by product, by region, by month.

I have written measures to give me the market share of each product within any region/combo of regions across any time period, as well as a rank measure to allow me to list out the top products based on the market share measure.

However, I am stuck on the next parts.

1. I want to have a measure that will return the Ranking (e.g. #1, #4) for one specific product in any region/time period. So the measure would be called [Product A Rank] and would always return the rank for product A (based on current region/time filters) regardless of which product was selected
2. Secondly, I want to have a measure that will return the name of the top (#1) product, again regardless of which product is selected but dependent on region/time filters. So if Product C is number 1 ranked within Region Y in Jan-16, then the [Top Product] measure would return the text “Product C”.

I will be hugely grateful if anyone can help!!

Thank you 🙂

freekeys

#5532

tomallan
Keymaster
• Started: 7
• Replies: 2556
• Total: 2563

Hi,

For 1., the approach that comes to mind is a formula within a formula. The inner formula finds the rank of all products within the current filter context, the outer formula sets a filter on Product A from the the table returned from the inner formula. The result of the filter is used as the first argument of a MAXX to return the rank captured in the innermost formula for Product A.

For 2., CALCULATE, VALUES and TOPN functions should get your result.

Do you have a sample workbook with anonymized data that you can post?

Tom

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.