3 Replies Latest reply on Sep 29, 2016 12:31 AM by ra.sae.0

    Runtime Aggregate Calculation with filters

    Shrikant Lambe

      Hi All,

       

      Need expert advice on how to achieve this view.

       

      Here I am trying to find out numbers of customers, respective Sales and Profit numbers, which is segregated by Region.

      This view also has Profit Breakdown for each Region with Customer level granularity.

      So ideally I expect #customers with all breakdown buckets per region.

       

      E.G.

       

       

      But When user selects any of the filters, it should re-calculate Breakdown based on filter selection

       

      In this scenario, one customer might have different breakdown status when measured at Product Name filter.

       

      Here is the breakdown bucket at higher level.

       

      Profit Breakdown per customer:

       

      If SUM([Sales]) > 0 AND SUM([Profit]) > 0

      Then 'Upper'

      ELSEIF SUM([Sales]) > 0 AND SUM([Profit]) < 0

      Then 'Lower'

      ELSEIF SUM([Sales]) > 0 AND SUM([Profit]) = 0

      Then 'Equal'

      ELSEIF SUM([Sales]) <= 0

      Then 'NetNew'

      END

       

      Appreciate help on this topic.

       

      I have attached my workbook for example.

       

      Cheers!!!