1 Reply Latest reply on Aug 23, 2018 5:24 PM by swaroop.gantela

    How to make calculated field that measures share of market

    John Lee

      Hi, I have volume data from 2016 to 2018. I put Provinces on columns, and products on rows

       

       

      For now, I applied year filter for 2016 and I did quick table calculation on share of market measure using % of total parent using table down. This got me the share of market of a product for 2016 in a given province. 

       

      Now, I want to be able to replicated what i did above by creating a calculated field without the filters. Ultimately, I want to be able to take share of market from 2017 and subtract by 2016 to show the change. Thank you

        • 1. Re: How to make calculated field that measures share of market
          swaroop.gantela

          John,


          This is certainly not the most straightforward or efficient

          ways to go about this, but maybe it can give ideas.

           

          Instead of using quick calculations, I tried Level of Detail calculations.

          These are easier to control, but may come at a cost to performance.

           

          The 2017 market share can be calculated as:

          { FIXED [State],[Product]:SUM(IF [YearOrderDate]=2017 THEN [Sales] END)}

          /

          { FIXED [State]:SUM(IF [YearOrderDate]=2017 THEN [Sales] END)}

           

          Likewise for 2016.

           

          The difference was:

          ZN([MarketShareStateLOD2017])-ZN([MarketShareStateLOD2016])

           

          with ZN changing any nulls to zero.

           

          Please see workbook v10.3 attached in the Forum Thread.