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