Power BI Challenge 7 - Purchases, Inventory And Sales!

[MudassirAli]

@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

[MudassirAli]

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!!

[BrianJ]

@amal.rebai1995,

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.

Great work!

– Brian

[Greg]

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.

Greg

[MudassirAli]

With courtesy of @BrianJ, here is the web link for my report:

Power BI 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.

[Greg]

Hi All:

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).

Power BI Report

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…):

NOTES:

What I liked:

• the colour theme and transparency of visual backgrounds I used; I think it looks great
• the “fake” navigation that made this single-page report (with many bookmarks) react like a multi-page report (via the extensive use, flexibility, and power of grouping in the [Selection Pane] to make assigning and updating bookmarks easier)
• the “bounce” of the button text and images; slightly changed font colour, size, padding, image weight, size, etc. to identify that an action can be performed
• the new “Smart Narratives” visual available as a preview feature as of the September 2020 release of Power BI Desktop; I’m really looking forward to exploring this further
• the “Horizontal Bar Chart” custom visual by Microsoft, as a technique to save space when presenting a bar chart by placing the category inside the bar instead of having axis labels
• the use of the “Top 10 and Average of Others” in said Horizontal Bar Chart; I think this provides a great insight as t let’s you know just how far the top 10 are above the average

What I didn’t like / needs more work:

• I used the whitespace character to try to hide the tooltip for the “Info” dialog open button and the “Close” button in the “Info” dialog, but they now show a portrait blank white tooltip … a complete absence of tooltip was the goal, but oh well…
• the scroll bar in the “Horizontal Bar Chart” custom visual used in the Sales bookmark section seems to be automatically set to a dark colour when a dark background is chosen; needed to increase the transparency of all visual backgrounds to 50% (I initially set everything to 25%, as I liked it better, but, again, oh well…)
• I got a bit lost in the interactions of the bookmarks and visibility of the dialog box, and on closing the dialog box, then display reverts to the [Purchase] bookmark, which was not the intent, so this is a “known issue” with this report
• the “Departures” table in the [Time] bookmark incorrectly shows all materials, including those that have not “departed”; ran out-of-time that I had allotted myself to pursue further, so this is a “known issue” with this report
• the visualizations are a bit sparse, but my focus was more on the presentation of the bookmarked data than on a full analysis of the data
• the fragility of the new Smart Narratives visual; I’m guessing the fault was mine, not the visual’s, but regardless, I was almost done when I decided to clean up a little and reorganized one of the measures used into a new table; this broke the Smart Narratives visual and I couldn’t recover it … I needed to start again from scratch. From now on, I’ll create my text in an outside editor, including placeholders for measures, then replace the placeholders with measures once at the end only; I’m sure this will get better over time and will shortly no longer be an issue
• only on final polishing did I publish to the Power BI Service for final testing, and with USERPRINCIPLENAME() now being active for the current user, the width of the sidebar is now qite enough; I’ll test the published report sooner in the development cycle from now on, but this remains a “known issue” with this report

Setup:

• used Power BI Desktop September 2020 release (Build 2.85.681.0, dated 9/22/2020)

Goals:

• to gain experience using the new Smart Narratives visual (only available as of September 22, 2020)
• to increase my experience with multiple bookmarks on the same page
• to increased my experience with “bounce” to give users an extra visual clue that a button can perform an action

Data Model:

• used eDNA extended date table as [Dates] Extended Date Table (Power Query M function)
• marked [Dates] table as a date table
• changed all date formats to dd-mmm-yyyy [by typing “dd-mmm-yyyy” into “Format” box directly]
• imported Invoices, Purchase, and Receipts as staging tables; disabled load
• renamed [Material] as [Material ID], so once a duplicate column had been created as [Material], could change type to “Text” and enable “Search” in a slicer
• deleted all auto-generated relationships
• arranged table model in a “waterfall” and used “Manage Relationships” dialog to add all relationships (all 1-to-many)
• created empty tables for [Key Measures] and [Admin Measures]; opened 2 sessions of Tabular Editor (1 each on my Challenge #6 PBIX and this file) and copied measures
• added [Last Refresh] table
• hid all ID fields
• added disconnected supporting table for [Materials plus Others]; accepted default of many-to-many relationship offered by Power BI when linking to [Materials] table
• imported tables from dataset as staging queries;
  • created references for Purchasing, Receiving, Invoicing
  • created reference (remove other columns, remove duplicates) for Customers
  • created reference (remove other columns, remove duplicates) for Materials; where a value was in more than one table (e.g., Material) created staging level 2 reference from each and appended all results (then removed duplicates) to ensure all values accounted for); duplicated the [Material ID] column to [Material] and changed type to Text so the “Search” option could be used in slicers
  • created reference (remove other columns, remove duplicates) for Purchase Orders from each of Purchasing, Receiving, Invoicing; as some PO numbers were present both on the Inventory and Sales sides, added a custom column and set the [PO Type] to “INV” for PO’s from the Purchasing and Receiving tables, and [PO Type] = “SALES” from the Invoicing table; duplicated the [PO] column to [Purchase Order] and changed type to Text so the “Search” option could be used in slicers; appended all results (then removed duplicates) to ensure all values accounted for)
• disabled load for all staging tables
• renamed columns to ensure consistency
• deleted all auto-generated relationships
• created relationships:
  • Customers[Customer] → Purchasing[Customer]
  • Materials[Material] → Purchasing[Material]; Materials[Material] → Receiving[Material]; Materials[Material] → Invoicing[Material]; all inactive
  • Purchase Orders[Purchase Order] → Purchasing[Purchase Order]; Purchase Orders[Purchase Order] → Receiving[Purchase Order]; Purchase Orders[Purchase Order] → Invoicing[Purchase Order]; all inactive
  • Dates[Date] → Purchasing[Purchase Date]; Dates[Date] → Receiving[Received Date]; Dates[Date] → Invoicing[Invoice Date]; all inactive

DAX Calculations:

• added “Admin” measures for:
  • [Is Desktop] using difference between NOW() and UTCNOW() to determine user interface
  • [Is Service] using difference between USERNAME() and USERPRINCIPALNAME() to determine user interface
  • [… Tooltip] measures to use [Is Desktop] and [Is Service] to display the appropriate tooltip regardless of interface

Visualizations:

Custom Visuals:

• Horizontal Bar Chart (by Microsoft)

General:

• used filter pane to restrict report to historical dates by using Dates[IsAfterToday] = False
• used “Work” pages to try out different visuals and identify trends that were interesting and should be included in the main report page
• formatted measures using eDNA DAX Clean Up tool https://analysthub.enterprisedna.co/dax-clean-up
• made extensive use of the [General] section in the [Visualizations] pane to ensure consistent sizing of all visual elements and adjusted the X/X/Width/Height as necessary; tried to keep multiples of 10 px and 20 px spacing between visuals
• made extensive use of the [Selection Pane], naming and ordering all visuals and assigning into groups, which eased the copying of “Work” page “groups” to the single visible page and subsequent assignment to bookmarks
• used internet search to find blue abstract background image;
  • (followed steps from @Jarrett’s YouTube video on choosing a colour theme https://www.youtube.com/watch?v=4gKm-AF1fdo)
  • used eDNA Colour Theme generator “Image to Colours” option and selected light and dark HEX codes (#48A8F0, #003078)
  • used Data Color Picker https://learnui.design/tools/data-color-picker.html#palette with the above-two HEX codes to generate a 5-colour palette (HEX #48A8F0, #3488D3, #226AB5, #114C96, #003078)
  • used Power BI Tips Theme Generator https://themes.powerbi.tips/ with these copied HEX codes to generate JSON theme file
• visual colours: set background colour to darkest theme colour 25% darker #00245A; set background transparency to 25%; set font colour to white for all (items, column headers, axes, data points, etc.)
• hid all pages in the report except for the single main page (i.e., hid “notes” page and all “work…” pages)
• added “Info” button in top-right corner to display info dialog and altered button text padding, button font, button colour, and button fill slightly between “default” and “on hover” (as well as icon colour and weight) to give the user a visual indication that an “action” can be performed
• added “Info” dialog with placeholder sample text

Sidebar:

• used buttons for false “navigation” (to fake the appearance of a multi-page report even though this is actually a single-page report) by:
  • activating bookmarks to show/hide the desired visuals;
  • altering button text padding, button font, button colour, and button fill slightly between “default” and “on hover” to let the user know an “action” can be performed
  • duplicated navigation buttons and set background to white, font colour to black for “active” navigation choice;
  • used selection pane and bookmarks to show/hide the appropriate “active” and “inactive” navigation buttons
• added Reset (slicers) button and altered button text padding, button font, button colour, and button fill slightly between “default” and “on hover” (as well as icon colour and weight) to give the user a visual indication that an “action” can be performed
• used “shadow” and slightly darker background colour to ensure buttons and slicers stand out on navigator
• added 210 px-wide rectangle background for sidebar and title textbox; grouped both in selection pane with title above background so would always appear in front
• used 2-column multi-row card at bottom to display report admin and session admin measures

Top:

• used Smart Narratives visual to display summary text and dynamic measures
• used line to separate Top portion (unchanging with bookmark) from Bottom portion (changing with bookmarks [“false” navigation])

Bottom - Purchases bookmark:

• used [Horizontal Bar Chart] custom visual to display top 10 materials and average of others (this visual saved horizontal space by placing the category inside the bar)
  • ([TopX] set to 10, so will show 10 bars for the Top 10 materials plus an 11th bar for “Others” [average of values outside the Top 10])
  • ref: https://www.proserveit.com/blog/ms-power-bi-topn-and-other

Bottom - Inventory bookmark:

• used pie chart to depict material movements for inflows vs. outflows
• used pie chart to depict value movements for sunk costs vs. sales
• used inventory summary table to show detailed quantity, value, and balance amounts
• used combo chart to show inventory balance quantity vs. inventory balance value

Bottom - Sales bookmark:

• as the was a large quantity of one material sold compared to all others, decided to break-out the top selling material into it’s own multi-row card, so that the Horizontal Bar Chart custom visual could be useful in presenting the relative quantity sold of the remaining materials
• used line charts for total sales and total quantity; noted the spike of material quantity sold between the 17th and 24th of April 2020

Bottom - Time bookmark:

• used table visual to present materials arriving in the warehouse, including their delivery days; used conditional formatting/data bars on delivery days
• used table visual to present materials departing from the warehouse, including their residence days; used conditional formatting/background on residence days

[alexbadiu]

Hello All,

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

Colour Theme

Please find below the theme used for this report. (photo and colours hex)

Power BI Report

Best regards,
Alex

[Hideo]

Hello Forum members,

Please let me share my report.

This time I took Q&A style. When I was thinking how do I build report with having brief copied on the canvas, I thought this is a good idea to following the questions so I did not change any one word from the three key focuses.

Q1 Average delivery is 36.1days and can see further by clicking the vessel button.
Simple bar chart but try to stand out with having orange line which is a spike of slow delivery items.
I have try to focus on delivery days by items to make sure we consider those delivery days for the next re-orders.
I have try to explain as much as possible by narrative so the consumer can choose fast delivery and slow delivery items by using the Days slicer in the basis of average delivery 36.1 days.

Q2 Location is on the right end to make the most of the space.
Average storage is 15.9days and can go further by clicking the truck icon.
Same logic with Q1 has been applied on Q2.
Again I have try to stand out for the slow moving items delivery by orange bar.
By considering the average storage 15.9days, we can pick up fast moving items and slow moving items easily by using the storage days slicer.
For those slow moving items we would consider purchase quantity and timing for the next re-order.
Vice-versa, we need to consider to order more for the fast moving items.

Q3
Prepared two answers.
For the answer 1, by clicking the first chart button, billing details chart pops out to show the billing details by date. Orange lines are highlights when the big bill spike happens. (no image to save screen)
The 2nd chart button for Answer 2 is interesting.(Image as below) Because by changing the TOPN material number, pie chart is going to adjust as per your selection. Also Answer 2 itself is changing according the number entered. Number 50 is set up as a default which means top 50 materials dominates 81.0% of total billings.

All the best for all the participants.
Thank you so much eDNA for giving me an opportunity to submit my report.

Power BI Report

Kind regards,

Hideo

[BrianJ]

@alexbadiu,

I absolutely love what you’ve done here. Your writeup underscores that great design is not just about your report looking good (which yours always do), but is a holistic process that guides every choice from the color scheme to the analyses presented to the visuals chosen. The way you integrate all those elements and consider them so thoughtfully from the perspective of the user is really unmatched. I know how much effort you put into the cohort analysis, and the decision not to use it must’ve been difficult, but it reminds me of how great chefs sometimes improve their dish by taking an element OFF the plate.

The black-white-gray theme is striking, and so dramatically emphasizes “refocus on the essential” theme of the report. Even upon initial glance, the immediate impression is that there is no fat here – nothing extraneous, and that everything serves a very specific and intentional purpose.

I know before Challenge #8 that I’m going to go back and read and reread this writeup multiple times to try to fully incorporate this type of thinking into my own reports.

Well done!

• Brian

PS: in your “how I did this” write up, I would love to see the Directors Cut Extended Version that includes what you did with the cohort analysis. I think you made the right choice not to include it in the main report, but I suspect it’s a very interesting analysis that still worth taking a further look at.

[datazoe]

Here is my entry: DataZoe Challenge 7 Power BI Report

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.

Data model (it looks like a fish so I didn’t want to change it ha):

Edit: I added in Customer last minute then I realized it didn’t actually work with the data model like it was. I fixed it this morning, and here is my new fish:

Thanks!

[datazoe]

@BrianJ I really liked that timeline! I wanted to do that too but couldn’t get it to work, so look forward to figuring out how you did it Great entry (as usual)!

[datazoe]

@MudassirAli Beautiful report, and loved the techniques you used! Really pretty, great job. I’ve been looking in to design ideas lately and you did a fantastic job with the colors in that you had the main color with most, then 2nd color with 2nd most, and third color with the least. I’m probably explaining it wrong, haha, but it looks great.

I have to admit, I giggled when I saw your acronym on top!

[BrianJ]

@datazoe,

Thanks – I really like yours as well. Terrific use of the box plot, and a big thumbs up on the way you’ve broken down known versus unknown. Plus, I laughed at “… looks like a fish”.

In terms of getting the timeline to work, the trick is to have a master transactions table and then unpivot it so that you have a single date column, and then a date category column that specifies whether the date relates to ordered, received or billed. I’m putting the finishing touches on my “how I did this” writeup that explains in detail the transformations needed to get that visual to work.

– Brian

[MudassirAli]

Hahaha I didn’t notice the acronym and I am laughing now. Hope I don’t get disqualified
You read the design perfectly and explained my design better than I did.
Great observation!

I like the way you have broken down the known and unknown costs plus the way you have showcased Sales in black color and Costs in Red.
Looking forward to your write up.

[MudassirAli]

Great job @alexbadiu in concentrating on a specific scope to explain to the end user efficiently. Moreover, you have also mastered the art of using dark colored themes now. The way you have designed your report, the first name that came into my mind who uses this type of dashboard style is @sam.mckay

Coming to the GIF part. What software do you use for that? I have tried many free versions and the results are horrible.

[alexbadiu]

Thank you @MudassirAli ! You are right that the dashboard style reminds of @sam.mckay ! His best practices, ideas and ways of creating reports inspired me and it comes naturally to me now. What is great about EntrepriseDNA is that in time, we could all become a little bit of Sam, and with these challenges maybe we will be also become a little bit of Brian, Melissa, Greg or MudassirAli
For creating the GIF I used a free software called : ScreenToGif.

[BrianJ]

@MudassirAli,

SnagIt also has great GIF creation capabilities. $50 seems steep for a screen capture utility, but use it for a day or two and you’ll wonder how you ever lived without it. You can get a free trial version here:

TechSmith Snagit | Screen capture and screen recorder

• Brian

[MudassirAli]

@alexbadiu that’s some high level flattery

You are right though. The goal is to match our teachers in excellence one day.

[MudassirAli]

@BrianJ I will give it a try. Hopefully I will be able to convince the usage of this software to management so they buy one for me
For tomorrow’s presentation, I am using my current challenge dashboard for a summarised view and details on the next page.

Let’s see how it goes.

[JarrettM]

I’m with @BrianJ on this one. $50 sounds a bit much, but once you start to use it, you will wonder what you ever did without it!

Thanks
Jarrett