Couple of notes about how I have approached the task:
The total hours calculation is based on the greater of the sum of:
Duration + Break from the timesheet data
A standard working day (7:30 hours)
E.g. if the client worked 8 hours, with a 1 hour break, total hours for that day would be 9
If the client worked 4 hours with no break, total hours would be 7:30
Utilisation is based on the Duration column divided by the Total Hours calculation as per above
Utilisation target is 80%
Target hours is the total hours in any period * Utilisation target and represents the optimal number of billable hours in that period
% of days worked is the number of distinct dates on the timesheet data, divided by the number of working days in any time period
Occasionally this is above 100% due to the client working weekends
Working days is based on UK bank holidays – could not find a reliable API for NZ holidays
I have assumed that the first and last date that a project appears is that project’s start/end date, and joined this info onto my date table to give a timeline of clients/gaps between jobs
I have estimated potential lost earnings using the following logic:
For any workdays where the client has not worked to the utilisation target, these hours are considered “Lost”
I have a separate calculation that works out the last hourly rate the client was being paid at
Earnings Missed is the sum of all the lost hours multiplied by the latest hourly rate
How this data has been presented:
Dash contains key metrics around earnings and productivity – can be sliced by year, client and task
Graph at the bottom shows earnings (actual and missed) as well as utilisation (actual and target) over time
Earnings by client is a simple table showing where the client’s income comes from
Dynamic Stats allows the client to view a selection of metrics and split them by four categories: Client, Project, Task, Year
Average rate paid per hour increases as cumulative client earnings decrease in most of the cases.
Work-efficiency per day is directly proportional to the number of hours the breaks are taken. For instance, Thursday registers the most efficient day for work, but in hindsight, it registers most hours for breaks (103) from the data between June 2018-September 2021.
So, more no. of breaks ensures more efficiency in work and vice-versa.
The highest cumulative earnings were done between the period of February 2021-September 2021.
@BrianJ - your work is outstanding !! awsome !! very impressive !! I’m really a fan of this I bookmarked and from time to time come back to the report to cheer myself up I’m especially impressed by the tooltip visualisation. It looks great !! However, the one thing I would suggest to rework - time pass visualisation are not recommended to go from up to down but rather from left to right.
The general comment on some visualisation released to this challange is that there are very stunning but difficult for interpretation. For example pie charts / donnuts or marimeko are difficult to quickly understand. For some tips follow https://www.ibcs.com/
Nevertheless, it is a great pleasure to look at yours reports
Thanks very much for your constructive feedback on my report. I always really appreciate and welcome that type of input. Following up on your comments, I did some additional research and I think your points and critique are absolutely spot on. I will implement those changes in the next version of this report.
I totally agree with you about pie charts being worthless, and have actually removed the Pie Chart option from my Power BI visualizations area:
I also try to steer clear of donut charts, but think in limited circumstances thinnk they can be valuable as slicers with 2 or 3 choices, clear labeling and a related metric card in the middle, which depicts a lot more info than a slicer and works just as well for that purpose.
Again, thanks for your input.
Brian
P.S. Your post ended up costing me $55, since based on the link you provided, I just ordered a copy of the “Solid, Outlined, Hatched…” book on IBCS standards.
When preparing the dashboard, I found the resources that explained the concept of Utilization to be very helpful. As I had no prior idea of the concept.
The dashboard consists of some basic bar charts, scatter plot and KPI cards. This part was easy.
Planning and thinking about the future scenario analysis is what took me some time to figure out what I wanted to do and how I wanted to share it.
My biggest learning was to be able to use text editor and embed it measures and be able to format the text, font size and colours etc. particularly the Recommendation area, where the suggestions are dynamic . Before attempting the dynamic text, I wasn’t sure if this would work, But then the Switch function came to my rescue.
Another thing that I always find a bit difficult is to figure out a correct background, and a colour scheme.
Although I am satisfied with the outcome overall, I find there is too much text in relatively small space as per my liking.
Thank you, Team, – for this great challenge. It really helped me put my skills to a test.
There’s a whole lot I really like about this report - first and foremost the scenario analyses that are focused on actionable recommendations. The first two questions I usually ask when evaluating a report are:
can I easily tell what the author thinks are the most important questions and the most important information to present? and;
are the conclusions/recommendations that result from that analysis clear and are they actionable?
I think you’ve done a really good job on both of these questions - you’ve limited your visuals to the most important info, and I love how you’ve used the SmartNarrative and switch functions to summarize the results and provide clear recommendations.
So, the hard stuff you’ve done really well. In terms of areas for improvement, I would suggest a few:
Your charts need titles. Especially since the bar charts look similar the user has to work out for themselves what they are both depicting.
The bar charts will work better as horizontal charts - it’s a much more readable style when you have long x-axis labels. The Microsoft Horizontal Bar Chart custom visual will work beautifully for this - allows you to put long labels under the bars rather than to the left of them. In terms of the vertical vs. horizontal issue, this article and accompanying video does a wonderful job addressing this along with a number of other common dashboard design mistakes - very highly recommended for everyone to check this out:
I agree with you that your color scheme could use improvement. I really like the dark background, but not all the colors work well together and in particular the white on green writing is very hard to read, which is a shame since those recommendations are one of the best features of your report.
I’m not particularly good with colors either, so this is where I let Analyst Hub do the work for me. On Analyst Hub, there are over 120 color themes posted from some of the best designers around, and you can download these and use them in your reports.
In addition, there’s a great tool in AH that allows you to create a color them automatically from a photo. One of the things you can do with this tool is to create a screenshot of a report you really like, and then run that screenshot through the AH tool and have it create a color theme for you based on that screenshot.
Overall though, excellent effort and with just a bit of tweaking, I think this can be an outstanding report.
Thanks for participating, and I hope this feedback is helpful to you.
Just wanted to let you know that your interactive help popups from your DC 16 entry were a huge hit when demo’d at last night’s Accelerator live session, which was focused on building a great UI/UX through buttons, bookmarks, tooltips, drillthrough and page navigation. Terrific use of buttons and bookmarks that none of us (including @sam.mckay ) had ever seen done in this particular way.
I’m especially impressed by the tooltip visualisation. It looks great !! However, the one thing I would suggest to rework - time pass visualisation are not recommended to go from up to down but rather from left to right.
