16 Replies Latest reply: Jan 30, 2018 4:02 PM by vidya sagar malla RSS

    Calculate Percentage of Multidimensional columns

    vidya sagar malla

      Hello Community,

       

      I want to create a Percentages based on the range of Amount by Year and Company. Please find the details below. I tried a couple of approaches but I am not able to achieve the expected result.

       

      Actual Table:

       

      YearCompanyAmount
      2010Apple10
      2010Nokia20
      2010Samsung30
      2010Apple100
      2010Nokia200
      2010Apple500
      2011Nokia100
      2011Samsung200
      2012Apple500
      2012Nokia150
      2014Apple600
      2014Nokia100
      2014Samsung150
      2014Apple300
      2014Nokia200

       

       

      Result Table: This is giving me the percentages of all the years.

       

      YearCompanyPlan SizeTotal AmountPercentages
      Totals3160100%
      2010AppleGreate than 50061019%
      Nokia50-7002207%
      SamsungLess then 50301%
      2011Nokia100-2001003%
      Samsung100-2002006%
      2012Apple50-70050016%
      Nokia100-2001505%
      2014AppleGreate than 50090028%
      Nokia50-7003009%
      Samsung100-2001505%

       

      Dimension Expression:

      Plan Size:


      if(aggr(sum(Amount),Year,Company)<50,'Less then 50',

      if(aggr(sum(Amount),Year,Company)>=100 and aggr(sum(Amount),Year,Company)<=200 ,'100-200',

      if(aggr(sum(Amount),Year,Company)>500,'Greate than 500',

      if(aggr(sum(Amount),Year,Company)>50 and aggr(sum(Amount),Year,Company)<700 ,'50-700',

      ))))

       

       

      Measure Expression:

      Total Amount:

       

      (sum(total<Year,Company>Amount))

       

      Percentage Amount:

      sum(Amount)/(sum(total{$<Year,Company>}Amount))

       

      But the Expected Result I want is to show the 100% split per each year.

       

      Expected Result:

       

      YearCompanyPlan SizeTotal AmountPercentages
      2010AppleGreate than 50061071%
      2010Nokia50-70022026%
      2010SamsungLess than 50303%
      Total 860100%
      2011Nokia100-20010033%
      2011Samsung100-20020067%
      Total 300100%
      2012Apple50-70050077%
      2012Nokia100-20015023%
      Total 650100%
      2014AppleGreate than 50090067%
      2014Nokia50-70030022%
      2014Samsung100-20015011%
      Total 1350100%

       

      Also, I will convert this table to 100% Stacked bar color by PLAN SIZE. So that I can see how much percentage of plan size for each year.

       

      Thanks in Advance,

      -Vidya