Multiple Files, Relationships and Calculations

[Spyderturbo007]

I'm having a hard time wrapping my head around the way to handle my scenerio and am hoping someone can point me in the right direction. I just need to know the bullet points and have no issues researching the rest.

I have multiple webstores selling different products and each webstore is married to a salesperson or organization. A salesperson is paid a commission, whereas an organization gets a fundraiser payout. At the beginning of each month I have to report commissions for the sales people and cut fundraiser checks to the organizations making a profit.

The data doesn't come out clean from the back end so it's typically 4 - 6 hours of work copying and pasting data. Then I have to go through a bunch of SUMIFS, XLOOKUPS, etc on the data. It's just a mess.

I really have two main files.

File #1 - Monthly sales data that contains order numbers, store name, order totals, sales tax, quantity of items purchased, etc.

File #2 - This is a manually maintained file that correlates the store name with the sales person or organization along with their associated payout dollar amount or commission percentage.

What I'm trying to do is build a workbook that will allow me to pull in monthly sales data, build a relationship with File #2 and then allow me to perform calculations. The link between the files is the store name.

I want this to be dynamic in the sense that I can do it each month pulling data from the newest Monthly Sales data with minimal effort.

I'm not sure if I should be researching building a data model, starting with a power query that's pointed at the folder, etc. Can someone point me in the right direction?

Thank you!

 

[Jeffrey Mahoney]

The simple thing to do is Keep your File #2 data in where your going to import File #1. Sheet2 is File #2, Sheet1 is File #1. Sheet3 is your formulas for combining the two data sets. As long as the data from File #1 is the same each time, no problem. If not, you would have to manually do some cleaning or write some code to help clean and sort.

Sheet3 is where you build Array based formulas to bring all the sales data and combine that with values from your Store details and calculate the $$ for Commissions and fundraiser payouts. Once the formulas have been setup on this sheet, they should be dynamic enough to accommodate the largest sales data import and you won't have anything to maintain. You can create summary tables off of this sheet.

I do this day in and day out. My $0.02

 

[Matt Allington]

I recommend you build a semantic model. Read my article here

The Optimal Shape for Power BI Data

It is common for self taught Power Pivot users to get the shape of their data all wrong. This article describes the optimal way to do it.

exceleratorbi.com.au

You should aim for a growing model, not a monthly tool. So load all the back data and then load the new data each month so it grows. It’s no more work and gives you lots of analysis benefits down the track. How you do this depends if your data consists of monthly extracts of just this month’s data, or monthly extracts of all historical data.

 

[Spyderturbo007]

The simple thing to do is Keep your File #2 data in where your going to import File #1. Sheet2 is File #2, Sheet1 is File #1. Sheet3 is your formulas for combining the two data sets. As long as the data from File #1 is the same each time, no problem. If not, you would have to manually do some cleaning or write some code to help clean and sort.

Sheet3 is where you build Array based formulas to bring all the sales data and combine that with values from your Store details and calculate the $$ for Commissions and fundraiser payouts. Once the formulas have been setup on this sheet, they should be dynamic enough to accommodate the largest sales data import and you won't have anything to maintain. You can create summary tables off of this sheet.

I do this day in and day out. My $0.02

The file structure makes sense, but I'm confused about how it all comes together. So I have my monthly reports in the folder (which have the same columns each month) along with the file I maintain manually. But how do they all get into Sheet3? Do I use Power Query to transform and load the data from File1 into Sheet1 and then from File2 into Sheet2? Then add the calculations to Sheet3?

If so, how does the data get updated when the next month rolls around?

Thank you!

 

[Spyderturbo007]

I recommend you build a semantic model. Read my article here

The Optimal Shape for Power BI Data

It is common for self taught Power Pivot users to get the shape of their data all wrong. This article describes the optimal way to do it.

exceleratorbi.com.au

You should aim for a growing model, not a monthly tool. So load all the back data and then load the new data each month so it grows. It’s no more work and gives you lots of analysis benefits down the track. How you do this depends if your data consists of monthly extracts of just this month’s data, or monthly extracts of all historical data.

I appreciate the link and read through the article, but PowerBI is something I've never touched.

 

[Matt Allington]

I'm not sure I understand what you are saying. Power Pivot and Power BI are essentially the same regarding this topic. The UI is slightly different

 

[Spyderturbo007]

Thank you both for the help. I've watched about 8 hours of videos on Power Pivot and DAX, so I have a much better understanding of how this all fits together. Now it's just a matter of learning expressions so I can do something with the data. Everything I've seen in the videos has nice and clean data that doesn't rely on things that may be True or False. I'm going to branch off into another thread to ask about my DAX questions. Thank you!