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

Help Tool Tips - 2018.02

Richard Michaels Feb 4, 2019 6:18 PM

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!

ToolTip.twbx 403.0 KB

1. Re: Help Tool Tips - 2018.02

Shinichiro Murakami Feb 4, 2019 7:53 PM (in response to Richard Michaels)
Not exactly sure because the numbers are not matching anyways.

Thanks,
shin

ToolTip_v2018.1_SM.twbx 411.2 KB
Like Show 0 Likes(0)

Actions
2. Re: Help Tool Tips - 2018.02

Paul Wachtler Feb 4, 2019 8:05 PM (in response to Richard Michaels)

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
Like Show 0 Likes(0)

Actions
3. Re: Help Tool Tips - 2018.02

Richard Michaels Feb 5, 2019 4:47 PM (in response to Shinichiro Murakami)

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!
Like Show 0 Likes(0)

Actions
4. Re: Help Tool Tips - 2018.02

Shinichiro Murakami Feb 5, 2019 5:09 PM (in response to Richard Michaels)
Like this?

Thanks,
Shin

ToolTip_v2018.1_SM_v2018.1V2.twbx 410.5 KB
Like Show 0 Likes(0)

Actions
5. Re: Help Tool Tips - 2018.02

Richard Michaels Feb 6, 2019 3:42 PM (in response to Shinichiro Murakami)

Boom! Thanks again Shin!
Quick question - do you offer private consultant work?
Like Show 0 Likes(0)

Actions
6. Re: Help Tool Tips - 2018.02

Shinichiro Murakami Feb 6, 2019 4:14 PM (in response to Richard Michaels)

Actually almost no experience, all depends.

if you have SNS message here
linkedin https://www.linkedin.com/in/shinichiro-murakami-4a57839b/
Tw: Shinichiro Murakami (@smurakam0201) | Twitter

Shin
Like Show 0 Likes(0)

Actions