1 Reply Latest reply on Mar 6, 2016 5:43 AM by Yuriy Fal

    Table calculations

    Eshwar Prasad

      Hi Team,


      Thanks in advance for your support.


      My requirement is like i need to get 5% Members.

      i did as below. Customer names sorted based on the sales in desc order.

      calculated 5% members like (5/100)*countd(Customername). i got 170.

      now i need to get that 170 members sales amount.


      To get that i tried like below.

      index()<=(5/100)*countd(Customername) will give boolean result then i can filter with true.. so that only top 170 rows will be remained.

      But i am not getting true value when i am trying to filter.


      And then i used windows_Sum(Sum(Sales)). But its not giving Proper result.


      I tried in this way but i am not able to get result. am i missing anything here?

      why windows_Sum(Sum(Sales)) is giving wrong result?


      if windows_Sum(Sum(Sales)) would give proper result can use this value in other views?


      Can any one help me to get this requirement..


      PFA for you reference.

        • 1. Re: Table calculations
          Yuriy Fal

          Hi Eshwar,


          Your original [5% Members] aggregate calculation

          gives different results depending on a view granularity.


          When no other dimensions are on a view

          (besides Measure Names),

          then your [5% Members] gives 170.


          When [Customer Name] dimension is on a view,

          then your [5% Members] gives 0 (see Sheet 1),

          and all subsequent calculations using [5% Members]

          could not give the results anticipated.


          To get it right one could calculate [5% Members]

          at the level of details excluding [Customer Name].


          Please find the attached wb w/mods.


          Hope it could help.