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

# Run Rate Calculation Using Parameter

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"
/
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?

• ###### 1. Re: Run Rate Calculation Using Parameter

Hi ashley,

use the calc like below

Calculation to use:

If ATTR([Budget])="Operations"

THEN

END

plz find the attached workbook

BR,

NB

1 of 1 people found this helpful
• ###### 2. Re: Run Rate Calculation Using Parameter

Hi Ashley,

Without changing the filters, the Operations Actual Spend is 55.1M not 27.5M.  Am I looking at something wrong?

Thanks,

John

• ###### 3. Re: Run Rate Calculation Using Parameter

In your data there are 475 rows for [Budget].

Are you looking to do this calc only for [Budget] rows?  And where you are using [Actual Spend] in that calc, are you actually looking to get the sum of all [actual spend] for all budget rows?

Or are you looking to do this calc on a row-by-row basis for all 475 rows, and then sum up the results?

• ###### 4. Re: Run Rate Calculation Using Parameter

Hi Naveen,

Thank you for your response, we are almost there. Included in the data under Cost Category 3, 4, & 5 are sub totals labeled "Result", see below image. I had tried to filter these out using the filters on Tableau but they still appear to be included in the calculation, this is why we are seeing 55M instead of the 27.5M I quoted in my original post. How would I filter out the "Result" line items from Cost Category 3, 4 & 5 ?

• ###### 5. Re: Run Rate Calculation Using Parameter

Thanks for your reply John, please refer to my response to Naveen. There are sub totals in the data under Cost Category 3, 4, & 5 that should be filtered out, but the filter does not appear to be "working" (i.e. I am likely doing it wrong".

• ###### 6. Re: Run Rate Calculation Using Parameter

Thanks for your response Joe, I would like to do this not for each line item, but for each major category under Budget. See below, using Naveen's help I have managed to calculate a Run Rate for Operations (its still wrong, do to the filter issue I mentioned in my reply to Naveen). Eventually, I would like to have a Run Rate for all budget categories (i.e. Logistics, CAF etc.)

• ###### 7. Re: Run Rate Calculation Using Parameter

Actually Naveen, found my filter issue, there was another subtotal hanging out in the data. All good! thank you so much for your help! Finally, how can I add a grand total for my RR Calculation? It doesn't seem to happen automatically?