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, 7 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: 2554
    • Total: 2561

    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.