Here’s Zoe’s entry for Power BI Challenge 7. @datazoe, feel free to add other details of your work.
Here is the link to the report:
Here’s how Zoe described it:
Includes tooltips on dots in the top right charts to show some detail, and breakdowns on the sales/costs numbers.
I also played around with the smart narratives . I kept the auto ones and added the median revenue per material.
To learn about the real-life scenario presented for the challenge, be sure to click on the image below.
Thanks @EnterpriseDNA for hosting this challenge. I did update the link this morning after fixing the Customer functionality/relationships in the data model.
DataZoe Challenge 7 Submission
The report was to answer three main questions,
It also had to be one page only.
The data was challenging for me, because there was a lot of unknown data. Many of the invoiced items had a PO that I didn’t have details on, including cost. I wanted to make sure I was clear on what was being shown and what was missing.
A purchase order can have many materials included, and they can arrive in partial shipments of materials on a purchase order, or just one of the many materials on a purchase order, at the warehouse. I chose to focus on the first receive date for each purchase order.
Similarly, the invoices could only some of the materials and also in partial amounts, so I chose to focus on the first invoiced date for each purchase order.
Late in the process I actually tried to change this to line item specific, instead of purchase order, so the first receive date for purchase order/material combo, and same for invoice date, but it was getting too close to deadline to fully explore it.
Days to First Received =
AVERAGEX(
'All Purchase Orders',
CALCULATE(
MINX(
'All',
DATEDIFF(
MINX( 'Purchase Order','Purchase Order'[PO date] ),
MINX( 'Received','Received'[Posting Date] ),
DAY
)
)
)
)
Here I am taking the first purchase order date by the po/material combo, the line item, and the first received date of the line item. I’m getting the days difference. I then am aggregating that by purchase order as the average of the line item days to receive for that purchase order.
The other custom visual used in the Box and Whisker by MAQ Software. I’ve used this one before and am always impressed by how it conveys a lot of data in such an effective way.
I also have a tooltip on all those dots, so you can hover and see some more detail. It would be more effective as a drill through, but I was trying to be very good on that one-page requirement. I’d love to change it to the timeline visual that @BrianJ used in his report for it though.
You can also click on any of the dots to see the rest of the page for that PO only.
Days to First Invoiced =
AVERAGEX(
'All',
CALCULATE(
MINX(
'All',
DATEDIFF(
MINX( 'Received','Received'[Posting Date] ),
MINX( 'Invoiced', Invoiced[Bill. Date] ),
DAY
)
)
)
)
Again, this is similar to the days to receive, but I am taking the first receive date and comparing it to the first invoiced date. I actually kept this on at the item level for an average.
The idea would be to keep it comparable to the days to start receiving. They are lined up so you can see at a glance where the time is spent:
Here is were I wanted to point out what was unknown in a very obvious way. I did this by bucketing the sales and costs into know and unknown.
Sales =
SUMX( Invoiced, Invoiced[ Unit price] * Invoiced[Tons Invoiced] )
Sales w / PO =
CALCULATE( [Sales],'All Purchase Orders'[Has Purchase Order] = "Yes" )
Sales w / o PO =
CALCULATE(
[Sales],
FILTER(
'All Purchase Orders',
'All Purchase Orders'[Has Purchase Order] = "No Associated Purchase Order"
)
)
Cost =
SUMX( 'Purchase Order','Purchase Order'[Unit Cost] *'Purchase Order'[Tons] ) * -1
Cost w / invoice =
CALCULATE( [Cost],'All Purchase Orders'[Has Been Invoiced] = "Yes" )
Cost w / o invoice =
CALCULATE(
[Cost],
'All Purchase Orders'[Has Been Invoiced] = "No Associated Invoiced Entry"
)
Revenue =
[Sales] + [Cost]
Revenue w / Known Cost =
IF( ISBLANK( [Sales w/ PO] ), BLANK(), [Sales w/ PO] + [Cost w/ invoice] )
I also added in the new smart narrative visualization. I kept the default things it provided, and added in the one thing I did add, which was the median revenue per material, broken down to also median price and median cost. It was neat but I found it a little difficult to use in the whole 20 minutes I spent on it. I usually do this type of thing in my reports via tooltips or the Enlighten Data Story custom visual.
These are to give you an idea of volume and timing.
At the bottom in it’s own section I have ways to drill into the data by Customer, PO, Material, and a way to eliminate some of the unknowns in your analysis of the data.
I’ve been looking at different techniques for report/dashboard design and I’m not sure it’s fully sunk in yet, haha! With this report I tried to make the central focus to quickly answer the required questions and that’s the focus of the 4 cards in the middle. I colored them to most and added shadows to make them stand out.
The other visuals are all supporting, so they are close to the primary visual.
I kept my colors consistent as well:
I created a common date table, and then also created a line item table (All) that matched to the purchases, received, and invoiced. To the tail of the fish we have the dimensions for the line items, all the materials, all the purchase orders, and the customers. They are titled “all” because there were purchase orders in invoiced that were not in purchase orders table, for example.
Customer was a late add, and I hid it because I wanted to show a “N/A” bucket instead of “(blank)” in the slicer. So to do that, I used a calculated column in the line item table.
Thanks for making it to the end of the write up, and if you want me to explain anything else, please ask!
Wow love it, really love what you’ve done here with some unique visualizations that no one else has used.
I also love how you’ve used different boxes right in the middle of the page to make key information stand out. The different colors also work really well together to highlight the dynamic measures embedded into those boxes.
The navigation experience is so unique here especially how you’ve placed the slices down the bottom of the page and also the way that you have created unique slices to filter your report in totally different and interesting ways to what others have dreamed up on this challenge.
I can just see so much creativity and thought gone into all aspects of the model build and also visualization here so really commend you on that.
Really appreciate the comprehensive writeup there’s a lot to be learned here for anyone who wants to learn some different storytelling techniques for their data.
Placing the tooltips on the dots within the box and whisker diagram is so epic as well. It really enables a user to see the outliers so much easier than some other visualizations I’ve seen (including mine!). But then the ability to dive into the more granular information is made seamless as well so really well thought out I believe.
Plenty of inspiration here.
Super work.
Sam
@MudassirAli YES! did you make that? My brain does not work the way Charticulator thinks my brain should work haha, so it takes me a long time to figure out how to get it work like I want. 
Lol…yes I did make that. I had plans to do this for challenge 6 but couldn’t make it work but I kept trying and now finally did it. Charticulator is the beast I must say.
That is incredibly cool. What does the filtering look like when you click on a particular state?
How long did it take you to build this in Charticulator?
– Brian
@BrianJ,
I have just started to learn Charticulator in detail so it took me a lot of time to figure this visual out. However, when you know how to design visuals in Charticulator, it wouldn’t take more than 15 to 20 minutes to design this type of visual. Frankly speaking, I am beginning to like this tool a lot.
When State is selected the visual looks like this:
When sliced by Region, it looks cooler:
I will try to use my own designed visuals in the next challenge. I have been searching around some epic R Script visuals but unable to implement them and I think this tool will help me in designing some of the visuals I have been looking for.
I’m curious as to the problems you’ve having creating graphics in R. Are you using the ggplot2 package?
My experience is that once you understand R’s “grammar of graphics”, each visual becomes the same basic “fill in the blanks” exercise.
Have you seen this epic cheat sheet?
https://rstudio.com/wp-content/uploads/2016/11/ggplot2-cheatsheet-2.1.pdf
The cheat sheet is amazing.
That’s the problem, I was trying to get the visuals to work without prior knowledge of R’s grammar of graphics. In particular I was trying to implement this visual found in the following website:
Now slowly starting to learn R but was just struggling to find the starting point.
Yeah, diving in without understanding the ggplot2 conventions would be extremely frustrating. The good news is that the structure is pretty easy to learn (at least the basics, and then you can just Google and/or cheat sheet the rest), and once you do you can apply the exact same techniques to every visual you create.
Two big tips:
Make sure you’re using R Studio - it’s a fantastic IDE and for plotting you can continually test your code in the plot window.
Check out the tutorial below. It’s part one of a great three part series on understanding ggplot2, with a ton of visual examples. Once you walk through this tutorial, you should have almost everything you need to create nearly any visual you can imagine. There’s also a short tutorial and a quick ref guide linked to the same page.
I hope this is helpful. Give a shout if you have any problems – happy to help.
http://r-statistics.co/Complete-Ggplot2-Tutorial-Part1-With-R-Code.html
@BrianJ that’s a great cheatsheet! I’ve only used R when I wanted to add stuff in like Margin of Error for survey results. I found R visuals to be slow and less dynamic so I kind of avoid them. Charticulator does not have these drawbacks.
Thanks – that’s great info. I haven’t had a problem with the R visuals being dynamic, but I have noticed in some cases they are a bit laggy in rendering. Will definitely take another look at Charticulator…
@MudassirAli oh that’s awesome! I made this thing last week too:
Just trying to figure out how to do some conditional formatting.
@datazoe
That’s great!
I think it would be easier to look at if the graph only shows top 20 and bottom 20 values.
Moreover, for the conditional formatting, did you try to use pallette colors in Charticulator?
I will try it myself too
You can click on the symbol and drop the measure Avg time spent in store in the fill section and then can select the colors of your choice for conditional formatting. It looks clearer after conditional formatting:
