Evaluate my Data Model/Table Structure!

[PowerBIzNess]

Hey everyone, hopefully this is the right place to post. Interested in any of your thoughts or suggestions on how I've structured my reporting so far.

I'm trying to introduce some reporting capability into our organization to enable quicker insights and more informed decisionmaking. Current state is reporting sent in excel monthly. My goal is to provide this data more dynamically but also add contextual information as well as a forecast.

Data sources

• Actuals - I have a list of metrics by person, let's say Sales. Updated monthly.
• Context Lookup - we track sales related activity by person each period. For example, staff member John Doe in the Hardware Department consulted with company X in March 2016 on Product A. Some staff may have single or multiple line items per period, while others may not have any at all (but still had sales - our list is not 100% inclusive).

I have a key consisting of employee ID and period linking the two tables so that I can chart sales by the various datapoints - department, customer, product. For staff that have no activity in the Context Lookup, I bucket them into a catch-all category.

For forecasting, we capture the planned activities for all staff. The piece that is tricky right now is the catch-all category. I'd like to take a running average of actuals in this category and use that as a basis for forecasting in future periods.

Thanks!

M

[Greg_Deckler · Accepted solution]

Check out these articles here for running totals, you should be able to modify them for running averages:

http://www.daxpatterns.com/cumulative-total/

http://www.powerpivot-info.com/post/109-dax-running-totals