Challenge Participants,
Below is my submission for Data Challenge #7. Of the seven challenges so far, this one was definitely my favorite. After a subpar performance in Challenge #6, I hit on a great inspiration for this one. My favorite non-Power BI YouTube deep dive is music mashups, where someone takes two (or more) artists with completely contrasting styles and combines them into a single song, that when done right creates something new and cool. So I wanted to try to do that with my entry this time, shouting out many of the great ideas I’ve seen implemented throughout the challenges and mash them up into a one-page report that fully answers the charge from @haroonali1000. So here’s the roster of tips and tricks that I mashed up:
@jarrettm - building color theme from a logo, conditional formatting via DAX SWITCH() returning hex values
Paul Ross - bar chart tooltip
@mudassirali - clickable info pop-ups on visuals, also incorporating parameter entry box into report
@michaelsampsonidis and @alvi - overall look influenced by their visual styles, which I really like
@alexbadiu - placing slicers in header to conserve space
@datazoe - incorporating notes about the data and analysis into the tooltip
@greg - dynamic narrative summary using Envision Data Story (I thought about switching this over to the new Smart Narrative feature, but decided to keep it a straight up shoutout to @greg’s reports that have a craftsman quality to them that I think is awesome)
@sam.mckay - inspiration from the 30 for 30 series on creative use of tooltips (I think I used every type of tooltip available in this report)
@melissa - the backbone of this report is a powerful data model I built entirely through techniques I learned from Melissa’s incredible forum posts and her Power Query YouTube video series (plus some basic assumptions I made about business practices regarding when a purchase order is considered completed):
This challenge was unique in the sense that prepping the data in Power Query and building a robust data model to support the required analytics was 90% of the battle. Everything went together quite easily after that.
One of the problems I ran into in Challenge #6 was reaching far beyond the scope of the brief. This time I tried to stay tightly within scope. Thus, per the charge, the left side of the report addresses in multiple ways only the elapsed time between ordered and received, and received and billed, while the right side analyzes billings.
The top narrative is dynamic based on the Purchase Order (PO) slicer, and uses the Envision Data Story custom visual. The second visual is a standard bar chart that uses the new September 2020 feature to show bar totals. I couldn’t decide whether to use total days or business days, so I figured why not both? To show the latter, I added a report tooltip with a single stacked horizontal bar chart, again using the bar total feature.
The next visual is my favorite in the report. It’s a really flexible timeline visual called Queryon Timeline. The problem is it is almost completely undocumented – no usable documentation on the company site, no YouTube videos, no postings on the Microsoft Community. Thus, it took me a while to figure out how to make it work, and then I combined it with the custom icon image hosting technique I stumbled onto in my Challenge #5 entry.
This timeline provides a spatial/visual representation of the elapsed times between the ordered date, the received date(s) and the billing date(s).
You can filter it down by material using the bar chart above:
Or go into focus mode to see the entire date history
Or click on the visual tooltip header to see a table of the dates conditionally formatted by the icon color
I modified Mudassir’s really innovative clickable “information dot” approach to show a graphic key explaining the icons
Okay, onto billings. Seeing that for most POs the total ordered quantity, received quantity and billed quantity differed by varying degrees, I created a “Tolerance %” what-if parameter to indicate within that percentage is close enough to call the compared quantities (ordered vs received, received vs billed) equal. If the two quantities are equal within the set tolerance, the icon returns an on target arrow, whereas if the second item is less than the first outside of the tolerance it returns the down arrow, and an up arrow if the reverse outside the tolerance.
Finally, I think the four-quadrant scatterchart provides an enormous amount of information in a single chart: margin, margin %, customer, purchase order, total PO duration and number of materials. This allows us to visualize a number of key elements regarding the billings across a range of dimensions:
- this is a low-margin business
- the longest durations tend to be associated with the orders involving a large number of materials
- customer U113 is associated with both the greatest variability in margin, and also generally long durations
- no other customers have a discernible pattern with regard to duration, margin or margin %
Thanks to those who made it through this long-winded writeup, to Haroon, Sam and the entire Enterprise DNA Team for a phenomenal challenge, and most of all to my fellow challenge participants – thanks for all you’ve taught me, all I’ve borrowed here from you, and best of luck on your entries. Can’t wait to see them!
– Brian