4 Replies Latest reply on Mar 6, 2018 7:05 AM by Renata Feher

    Display Rank without Displaying Members

    Renata Feher

      Hi All,

       

      I might have a fairly obvious question but for some reason, I cannot find a solution for this problem.

      Given a dataset which contains revenue data by company, year, quarter and region. I am interested in the achievements of Company D

       

      I am trying to have a table which shows a regional summary of:

      - the revenue of the whole region (for all products, all companies)

      - the regional sum of a specific product (Product D, all companies)

      - the regional revenue of Company D for Product D

      - the rank of Company D in the region for Product D revenue

      - the market share of the Rank 1 company in the region for Product D (market share = (total regional revenue of the highest ranked company for product D) / (regional revenue of product D)

      - the name of the Rank 1 company in the region

      - market share difference of Rank 1 Company and Company D

       

      In the view I only want to show:

      - rows: regions (which can expand to countries)

      - columns: the above calculations

      as table.

      I cannot show the name of the different companies in the row or column.

       

      I am having difficulties calculating the ones in red. Basically, I couldn't figure out a way how to calculate the rank without having the companies in the view.

       

      I would really appreciate any guidance here.

       

      Attached you can find a sample dataset.

       

      Thank you,

      Renata