6 Replies Latest reply on Feb 6, 2019 4:14 PM by Shinichiro Murakami

# Help Tool Tips - 2018.02

Hello All,

I hope this message finds you all well!

I know how the math is supposed to work; however, I'm having a hard time implementing a dynamic solution.

On the attached, I need help creating 2 calculated fields that will be used on the tooltips:

1- YTD target achieved

- Running Sum of my measure (MRR) / the Target bar on Sheet 1 (2)

- For example, the YTD achievement for September 2018 would be 5.2 million / 49.1 M. For each month, the denominator will be the target bar on Sheet 1 (2)

2- Remaining FC Achievement (in %)

- Monthly Target (ARR Target) - Monthly (MRR) /   remaining months in Fiscal Year - our fiscal year ends in September

- For example, for March 2018, the formula would be (10.5M - 919K) / 6

Any and all help is much appreciated!

• ###### 1. Re: Help Tool Tips - 2018.02

Not exactly sure because the numbers are not matching anyways.

Thanks,

shin

• ###### 2. Re: Help Tool Tips - 2018.02

Hi Richard,

For YTD Target Achieved you can use this:

RUNNING_SUM(SUM([MRR]))/[ARR - Targets]

For Remaining FC Achievement, the way you describe it can be calculated like this (this accounts for the fiscal year starting in September):

([ARR - Targets] - sum([MRR])) / datepart('month',dateadd('month',-8,datetrunc('month',attr([Date]))))

However I don't understand the "(in %)" part of that field name.  The results are mostly over 1 million for each month for that field so I'm not sure what you're looking for a percentage of.

Best,

Paul

• ###### 3. Re: Help Tool Tips - 2018.02

Hi Shin,

Thanks so much!

Your solution for Remaining FC Achievement (in %) is perfect! Thanks!

I do not think I explained the YTD target achieved formula the best.

In the denominator, I always want to see the September 2018 ARR Target value which is 41.9M regardless of the month. Is there a way to always have this in the denominator without hard-coding it in? I need this as my real workbook has multiple years worth of data in it. Thanks!

• ###### 4. Re: Help Tool Tips - 2018.02

Like this?

Thanks,

Shin

• ###### 5. Re: Help Tool Tips - 2018.02

Boom! Thanks again Shin!

Quick question - do you offer private consultant work?

• ###### 6. Re: Help Tool Tips - 2018.02

Actually almost no experience, all depends.

if you have SNS message here

Shin