4 Replies Latest reply: May 21, 2017 6:46 AM by Robin Hausdörfer RSS

    splitting yearly budget to months in script?

    Anat Dagan

      i have 2 tables:

      one containing actual spending and earnings per category per month e.g:

      monthcategoryAmount
      Jan 2016Raw materials-20000
      Jan 2016Salaries-35000
      Jan 2016Income40000
      Feb 2016Raw materials-22000
      Feb 2016Salaries-36000
      Feb 2016Income50000

       

      and another containing a yearly budget per category e.g.:

      YearCategoryYearly Budget
      2016Raw materials120000
      2016Salaries500000
      2016Income 1000000

       

      I need to display the actual amounts vs. monthly budget by dividing the yearly budget by 12 in a pivot table and in a bar chart with the actual amounts as bars and the budget as a marker for each month.

       

      If I add a measure to the pivot table dividing the budget amount  by 12 : Sum(YearlyBudget)/12    the total figures in the pivot table don't add up the 12 monthly amount into one yearly amount

       

      How to i turn the budget yearly data into  a 12 monthly rows per category and add a month name to each row directly in the script? of if there is another alternative for displaying the data correctly i will be happy to hear about it .