7 Replies Latest reply on Aug 14, 2019 4:09 AM by Ashley Skinner

    Run Rate Calculation Using Parameter

    Ashley Skinner

      Folks,

       

      I am hoping you can help me out, I am an amateur Tableau user. I am struggling with the below calculation for a run rate and year end outlook. I would like to have tableau calculate Run Rate and Year End Spend Outlook based on the below formula

       

      For each Budget Category, Budget is a Measure, calculate:

       

      (Actual Total Spend - Adjustment Parameter)/Current Month * 12 + Adjustment Factor

       

      Basically the adjustment factor accounts for a large purchase that isn't reflective in our Run Rate calculation, so we subtract it out to calculate our monthly spend. However when I do this in tableau, Tableau subtracts this one time spend from each row of data over and over so the total ends up being super negative, like 5 Billion. For simplicity, for my operations budget I am hoping to see the below total:

       

      (27.5-15)/7 * 12 + 15 = 36.4

       

      My Syntax is as follows:

       

       

      If [Budget] = "Operations" 
      Then (([Actual Spend]-([Adjustment Parameter]*10^6)) 
      / 
      [Current Month])*(12)+[Adjustment Parameter]
      END
      
      

       

      My data pull is a snap shot in time (i.e. Year to Date Spend based on the current month) so I cannot use the forecast feature. My end goal is to have a dashboard where the user can input the Current Month & Adjustment Factor Parameters

       

      Any thoughts?