Latest Enterprise DNA Initiatives

Power BI Challenge 7 - Purchases, Inventory And Sales Entry from Timothy (non-member)

Here’s the entry from one of our non-member participants, Timothy.




Here’s how Timothy described it:

Having missed the first six, I was determined to get one in. It is great to see all the different examples out there. This one was definitely a challenge from getting the data to a more usable format. The approach I took was:

  1. The Purchases and Receiving Tables were joined in Power Query; However, this was just an intermediary step as there were transactions for the same PO and same Material on the same day. This table was therefore hidden, and a new table (Purchasing and Receiving Summarized) was creating using the SUMMARIZE function in order to consolidate any transactions on the same day.
  2. In reviewing the Billing table, there also appeared to be transactions on the same day for the same PO and same Material. A new table was created also using the SUMMARIZE function called (Billing Summarized)
  3. In order to obtain unique PO numbers, a table was created in Power Query to only keep the “Purchase Order”, “PO date”, and “For Customer” fields. All duplicates were then removed.
  4. A separate table of unique Material values was also created.

As for tracking delivery times – this was definitely a challenge. I opted to develop a table to show when portions of the material was delivered.

I also added drill through pages to provide details on each PO (second picture), Material (third picture), and Clients (not shown to save space).

I tried to separate the report into three sections. The first, “Purchase Order to Shipping Details”, shows how much time between the PO and actual delivery. However, because the materials appear to be arrive in multiple shipments, I created a table that breaks up the % of materials received and the average days between those categories. I also added a chart to provide shipping time for each material. To obtain information on any specific material, a user simply has to select one of the materials on the chart and the “View Details” button will active and take the user (via drill through) to another report – which is one of the other pictures.

The second section, “Customer Billing Details”, provides the billing turnaround time. For this, I wanted to see how many times billings were occurring in various time categories (i.e. 0-5 days, 6-10 days, etc.). It appears that the “> 15 days” has the most occurrences so that would be an area to look into. There is also the average time listed for each customer. And – more details on the customer can be found by selecting a customer in the table which will activate the “View Details” section.

One other item that I was interested in was provided the user a way to view specific information on each PO. On the left side/filter section – there is a list of PO numbers. If a single PO is selected, then the “View PO Details” button activates and that allows the user to view specific information on the PO itself

To learn about the real-life scenario presented for the challenge, be sure to click on the image below.


1 Like

Thanks for getting involved in the challenge. I can see that you’ve followed the brief of the challenge really well and definitely have answered a lot of the main requirements. One thing though is you wanted in this challenge to try and keep your report to one page. So it took a bit more creativity around the visualizations.

Anyway, you have done some really unique things here that I like as well. Especially the multi tab approach to some of the visualizations especially the ranking ones. Really like what you’ve done there and that’s something quite innovative.

Definitely the colors is one element I wouldn’t want you to improve for next time. The horrible aqua green custom color that Microsoft has in the palette is certainly not one of my favorites. Make sure to try out some of the color tools that we have in the analyst hub.

I like how you’ve used grids around the report. Maybe think about having some clearer page titles or consistent navigation areas next time round.

Great to have you involved and really looking forward to some future submissions