August 9, 2016 at 7:24 pm #5528
- Started: 1
- Replies: 0
- Total: 1
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.
- 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
- 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 🙂
freekeysAugust 10, 2016 at 12:58 am #5532
- Started: 7
- Replies: 2556
- Total: 2563
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?
You must be logged in to reply to this topic.