3 Replies Latest reply: Sep 28, 2017 4:46 AM by Pratyush Shastri RSS

    Sum based on condition with dimension

    Radu Panciuc

      Hi guys,

       

      I have some cost data in which I have the cost, the date the cost was posted (Posting Month) and the date the cost actually happened (Actual Month).

       

      I would like to display in a table (Dimension is Posting Month) the % of cost that happened and is being posted in the same month (i.e. Posting Month = Actual Month).

       

      Normally, I do this with Set Analysis but I am not sure how I can incorporate the above condition (Posting Month = Actual Month). For a single month (in this case June 2017) my formula would be:

       

      Sum({$< [Actual Month]={'2017 06'} >} [Cost])/Sum({$< [Posting Month] = {'201706'} >} TOTAL <[BA]> [Cost])

       

       

      Posting MonthAmount (€)Amount (%)
      201701100,0000%
      201702100,0000%
      201703100,0000%
      201704100,0000%
      201705100,0000%
      201706100,00046%
      201707100,0000%
      201708100,0000%