You can assume the first shipment, however no issues if you have done whole quantity.
You can assume the first shipment, however no issues if you have done whole quantity.
how you made your Data Model and relationship between tables ?
I used Lookup value function to retrieve the values from tables. I was short on time so didn’t really dig in to establish bridging tables to connect the tables. For this task, it worked fine though.
Wow very impressive once again. Well done
Just a fabulous and inspiring design.
This is where I’ve got to.
Might put the finishing touches on it in the coming days
Here’s how I’ve constructed the model.
Hopefully this can assist you all in simplifying down the data.
Not too difficult.
I fear the data is a bit incomplete and very random, but don’t let that stop you diving into the challenge, still plenty to test yourself on.
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:
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!
Wow Brian Seriously awesome work! Love the design and to say its 1 page the level of detail available is amazing.
Love how you have called out the hints and tips you have picked up on this journey. Shows you the incredible talent we have in our community and how we all bring something different to the table.
Thanks for submitting Brian an amazing entry and knowledge share.
@MudassirAli you seem to be raising the bar every week!!! Awesome submission and creativity love the use of tool tips.
Good luck in your presentation.
Hopefully we can see a write up on how you went about creating this report when you get time :).
Wow, so good.
This is my report for this challenge, it looks like a simple report but the number of measures created was very important.
I displayed in cards: total sales, profits and costs
avg days taken to receive the orders & avg days taken to bill the orders
Count purchased, received and invoiced orders
and quantity purchased, received and invoiced.
I added a table to detail each order: the Purchase date, First and latest date Received, and the number of days between the order and the last entry.
Also I displayed the First date billed and latest date billed, and the number of days between the first entry at the warehouse and the last invoice date for each order.
To see tooltip for detailed materials by purchase order, hover over the visual tableau
I created 2 scatter charts
the first to show the average days taken to arrive a the Warehouse by PO compared to quantity sold
the second to show the average days taken to bill the customers by PO compared to quantity invoiced
I segmented the “purchase to receive period” into groups of days to display the costs for each period
also for the “receive to bill period”, I divided it into groups to display the sales for each period
@amal.rebai1995 I know I don’t get to vote on any of these but in my humble opinion this is the best one I’ve seen for this challenge.
Very nice work.
@GuyJohnson wow That’s very nice of you to say so, thank you so much.
it’s a simple report, I would have liked to develop it even more
Thanks @haroonali1000 for the comments. Every time I think that I am out of ideas for the next challenge but come automatically when you take the challenge seriously. These ideas never ever come when designing work-related dashboards because no one is looking at it critically.
As for the detailed write up, it has been done.
@BrianJ To be honest, to understand the work you have done and to learn from it, I need some time to actually digest it. You have come up with an excellent way to implement the different techniques shown in the forum in different challenges. The way you mashed the data via power query is really cool and would love to see how you did it. Frankly speaking, I always used power query extensively so wouldn’t have to create complex DAX formulas but now I am doing the other way round to learn DAX more.
Honestly speaking, no one comes close to the way you do the analysis and always bring different perspective to look at. So as far as the analytical aspects are concerned, you could have won every challenge.
However, I would only advise you to be daring with color formats as @alexbadiu and @Greg did in the previous challenge. The current report seems like it has been designed in Power BI but some reports in the previous challenges looked like as if they were designed in high level designing softwares.
Nonetheless, thank you so much for completing the challenges as there are many invaluable things to learn from you every time
Great Submission I really love the way you categorized Sales by Billing Group & Cash by Receiving Group based on range of days. Incorporating details on the heading section also looks very cool. You could have used the same color theme on the Table Visual as it would have given the nice uniform look.
Very good submission overall!!
Terrific entry. I feel like you made really smart choices all around on this one – in terms of color scheme, the choice of visuals, and particularly the choice of measures and metrics to characterize the key questions from the brief. One of the things I felt was challenging in this one was there were so many directions to go analytically, but looking at your report I feel you really captured what would be important to a manager trying to understand the dynamics behind the ordering/receiving/billing process.
As I said to @MudassirAli, I also feel your entry would benefit from people being able to use it hands-on. If you don’t have the ability to publish to web, let me now I’ll be glad to do it for you.
I just want to put my two cents in … first of all, thanks @MudassirAli for the props … I’m trying to push myself from a design point-of-view with every report (and challenge), and its rewarding to see it’s being noticed. I do, however, want to comment on your note to @BrianJ wishing for more colours: I’m a data guy by training and by experience and more than echo your first thoughts for Brian’s work … I can’t say enough about having good quality, well-analyzed data upon which to make decisions, and, as you said, Brian excels at that. I’m focusing a bit more on the design aspects at the moment, because, as a consultant, I most often work with people who already know their data inside and out, and are sometimes challenged in the way they’ve traditionally presented that data. That being said, I do and always will subscribe to the axiom “you can’t make good decisions from bad data”. So I look on presentation as more of a bonus than the primary goal. That being said (2), I’ve also had clients who’ve said “if your design is off, how can I trust your data?”, so both are important. All of which is to say, it’s like the consultant’s standard answer, “it depends”.
Thanks everyone for their patience in reading this … I actually intended only a short note, but ended-up rambling for a bit. Congratulations to all who have pushed themselves on this and previous challenges. My submission is in its final stages, and I hope to have it completed later today or tomorrow.
With courtesy of @BrianJ, here is the web link for my report:
The bookmarks aren’t working as the Sep 2020 update messed up the bookmarks. However, the file I submitted to EDNA team has the bookmarks working perfectly.
Here’s my submisstion for eDNA Challenge #7. I opted to use many bookmarks to make a single-page report look like a multi-page report. I had a blue-based colour theme initially, but @JarrettM’s timely video this week provided much better results, so thanks. Also, I was a little later completing my submission than I had intended as I anted to spend some time with the new Smart Narratives visual (available since Sep 22).
Here’s the notes I took during development (I hope all are still applicable; I was a bit rushed to get this in, so please bear with me … my editing might be off a bit, but anyway…):
Please find below my submission for Challenge n7.
I tried to focus on the essential and be clear on the message I wanted to send.
The first decision I made was to concentrate only on the POs and the corresponding Materials that are present in Purchase/Received & Invoiced Table. This is the reason why the numbers might seem a little bit different then in the other entries.
The second (difficult decision) was to decide what are the best visualizations to choose in order to be clear, concise and still provide flexibility to the end user.
My main problem was that in a 1 page report it is more difficult to imagine and create a story. I did not want to create a page of only KPIs and information spread across the page.
As in every report I want my audience to know or do something. I summarized the whole report into one sentence that I put in the middle of the Report:
" Refocus on the essential "
I spent a lot of time trying different approaches to present the data but finally the best combination I found is using the following charts:
The Pareto Chart
I placed the Pareto Chart strategically in the top of the page. It is so central that is difficult not to see it from the start. I ve chosen that place because that is where my story starts.
What are the materials you want to see and analyze? Doing so, I do not put the end user analyze all the data, I encourage them to take a certain path.
And for me, the best path is to start with volume
The GIF below can show the most probably path the end user might take.
The Scatter Charts
The Scatter charts have many advantages. They are easy to understand, and provide a lot of information if you use it with groupings, good X/Y axis, tooltips and interactions.
Once again, I found the combination of the scatter charts and the Pareto to work very well.
I think I did not do anything too difficult or extraordinary in this challenge, but I spent a lot of time thinking about details and trying all kinds of analysis. In the beginning I wanted to go with cohorts, spent a lot of time on that, and with Brian’s help I succeded to create it… but still I had to let it go.
This challenge for me was about simplicity, about going to the basics, at taking the time to think and present something clear and simple.
In one sentence I summarize my analysis, my recommendantion and the call for action
REFOCUS ON THE ESSENTIAL
Please find below the theme used for this report. (photo and colours hex)