Power BI Challenge 7 - Purchases, Inventory And Sales Entry from Mark

Here’s Mark’s entry for Power BI Challenge 7. @markperrone, feel free to add other details of your work.

image

image

Here’s how Mark described it:

DATA MODEL

Like others noticed PO and Material were in every table but also the customer because its tied to the PO. Where customer was not specified, I used “Unknown”. I cheated a little by creating the PO, Material, Customer table in another excel sheet. I could have done this with power query but if this were a database, I would have done this with SQL.

So, we have a conforming dimension which ties to all the fact tables (purchasing, receiving and billing) and lets us drill through across the different fact tables. We can use bidirectional relationships because there are no ambiguity issues, and we adhere to strict 1:M rules

I wanted to see how each fact progressed over time and since each fact has a date, I created an Activity fact, each date we see what happened across purchases, posting and billing.

The other activity was the days between each process – purchasing to posting, posting to billing along with the first/last date of each process – all this broken down by customer, PO and material. This is Activity 2 (didn’t take the time to come up with a better name for this table).

VISUALS

I concentrated primarily on the requirements but also considered “what would I want to see” and came up with the avg purchase-to-post visuals with the top & bottom 10 performers. I like to see KPI at the top, visuals next and finally some aggregated detail.

FILTERS

I implemented a pop-up filter to save screen real-estate. Straight-forward technique using bookmarks, selection pane and images. Here is the link to video https://www.youtube.com/watch?v=7OjY6bcyMg4 . This can also be done with buttons, but I thought the images were a little easier to work.

NOTE: I noticed there were 3 large spikes in the billing would have skewed some of the charts, so I suppressed them in the model.

OBSERVATIONS

Other than unknown, all customers were in the red.

VERSION 2 - I’ll be re-visiting this after checking out some other examples of business processes - cash flow, supply chain, etc.

Thank you for sharing your data model for this challenge, please don’t hesitate to share more value to wherever you think you possess the experience or knowledge that can help others in our Group. We appreciate the initiative and your help in this group!