Dashboard with sparklines, bullet charts and last month's figures

[asajnani]

Hi there,

I'm trying to create a dashboard but am a little bit stuck. The objective of the dashboard is to show a manager's performance by month.

I'm stuck on three parts of the dashboard:
- I want to create an in-cell sparkline which will automatically update based with each month's newly added figures
- I want to include last month's figure to compare it to this month's
- I want to create an in-cell bullet chart to indicate this month's performance and to see if the manager has reached their target

As mentioned above, this dashboard needs to be manager-specific and I don't want to go fiddling each month with the formulas; I've set up slicers in the Pivots tab so that the data is refreshed by manager and month (see the dashboard attached).

Any help or advice you could provide would be very much appreciated!

Thank you a thousand times in advance!

Cheers,
A

 

[Chihiro]

The way your current sheet is set up. You have values in Pivot Table Labels. This does not allow for much flexibility in dashboard display.

Instead, move values over to VALUES field and move Month to Rows (labels).

By doing so, you can use GETPIVOTDATA() function to dynamically update the table.

Something like...
When Date is stored in H6.
=GETPIVOTDATA("Average of Doctor Satisfaction - Rounding Information",$Q$28,"Month",DATE(YEAR(H6),MONTH(H6),1))

You'd also want to set up table for 12 month data as well (unless Pivot Table is set to contain info for single year only), in order to do spark line chart. As you need data series.

Normally, I'd recommend having all control on the dashboard itself so that user does not need to play with anything else.

Don't have time to give you sample this week. But if you can wait, I can give you sample set up next week.

 

[asajnani]

Hi Chiro,

Thanks for your help!

I've sorted out the sparkline and last month's figures issue but am stuck on the chart with target.

I'm trying to create these blue bar charts with scatter but can't get the scatter markers to align with like it does here: http://chandoo.org/wp/2011/03/24/kpi-dashboard-revisited/

Any ideas?


Thanks!

 

[Chihiro]

You are welcome.

Not sure about scatter markers, you can try alternate approach.

See posts in below link.
http://chandoo.org/forum/threads/column-vs-bar-chart-target-line.25132/#post-150785

My post shows trend line method, Narayan has link to method converting data series into vertical line.

See below for link to articles explaining different chart types to compare Actuals vs. Targets.
http://chandoo.org/wp/2009/12/18/charts-to-compare-targets/