Data Modelling Workout 07 Many-to-Many Part III

[BrianJ]

Estimated difficulty level:
3.5 out of 5

INTRO

This workout is a continuation of our series on effectively addressing many-to-many relationships in your Power BI data models.

Originally, @Greg and I had envisioned this as a three-part series. However, we decided to add this additional week’s workout to the series to give you additional practice with this essential technique for resolving mismatched granularities across tables by reallocating amounts to finer level granularity.

OBJECTIVES AND INSTRUCTIONS

This Workout is a re-do of Workout 06, with a different set of assumptions regarding how to reallocate the monthly Sales Targets to a daily granularity.

There are only two differences from Workout 06:

1. In the attached PBIX, I have incorprated a holiday table into the Enterprise DNA Dates table used within the model.

2. More critically, in Workout 006 we assumed that daily sales were roughly similar and thus we could allocate the monthly target to daily by dividing it by the number of days in each month.

The goal of this workout is to fix the granularity mismatch between the Dates table and the Monthly Sales Targets table by allocating those targets down to the daily level.

But this time, instead of assuming a constant target for each year and month, we will perform our allocation of the monthly targets based on a more complex and realistic ruleset from our Sales Forecasting Team.

They have analyzed 10 years of historic sales data and have come up with the following findings regarding the attributes associated with days that have significantly higher (or lower) total sales.

Here is a summary of their findings:

What this means is that a Wednesday is expected to have 12.0% higher sales than a non-Wednesday and thus the monthly sales target on Wednesdays should be 12% higher than “normal” days.

A few guidelines in applying this table in your allocation:

A given day only gets a single adjustment, and the priority is determined by the absolute value of the % adjustment in descending order (the list is already sorted this way). So, for example suppose Christmas falls on a Saturday in a given year in the data. Three adjustments would be in play here:

• Holiday
• Day 22-28 of the month
• Saturday

In this case you would apply the Holiday adjustment (+8%) since that’s the highest “ranked” adjustment with the greatest magnitude impact.

Here’s another example: A holiday that falls on Thursday Nov. 11. In this case, only two adjustments would be in play:

• Holiday
• Day 8-14 of the month

In THIS case, you would apply an adjustment of -9%, since the Day 8-14 adjustment is higher “ranked” given that absolute magnitude of the impact is greater (9% vs. 8%)

HINT - In these sorts of allocation exercises, I find it helpful to assign the “base” day (i.e., a day that meets none of the adjustment conditions) 100 points, and then adjust those points up or down each day based on the adjustments table rules.

Once you have properly allocated the specified monthly sales targets to daily, connect that table to your data model (in a one-to-many unidirectional relationship) and produce the following dynamic report page that shows the actual totals sales and total sales targets at monthly, weekly and daily granularity.

NOTE: You don’t have to have completed Workout 06 to particpate in this workout, but if you haven’t we would recommend at least reviewing the problem and specified solution before tackling this one.

Good luck!

P.S. I’ve confirmed that you can do this all within the Power Query UI, but feel free to use custom M code (or even DAX, though I would recommend PQ…) if you prefer.

Data Modelling Workout 007 - Many to Many Part III - Posted Workout.pbix (1.2 MB)

Data Modeling 003.xlsx (1.4 MB)

[jtej001]

Bit of a messy solution on my part, looking to clean it up but here is the results

Power Query

Data Model

Data Modelling Workout 007 - Many to Many Part III - Posted Workout.pbix (1.3 MB)

[BrianJ]

All,

The purpose of this workout was to demonstrate a general approach for allocating coarser granularity targets (e.g., monthly, quarterly, etc.) down to the daily level but relaxing the assumption that the allocation would be uniform over the period.

In this case, I provided a fairly convoluted set of rules and priorities for allocating the monthly targets down to the daily level. But when you break it down step-by-step, it’s actually quite straightforward and what I’ll walk through here provides a very generalizable approach you can use for many different allocation patterns.

So, because we will want to build a daily allocation table over the full period of the analsysis, we can start with a duplicate of the Dates table and build and revise from there…

Here are the allocation rules I provided.

Because of the power of the Enterprise DNA Extended Date table, the IsHoliday and “Day of Week Name” fields will take care of most of these conditions. The one we will have to handle using a custom column is what I called WeekCycle, which is whether a day falls in the 2nd, 4th or 5th week of a given month.

So, the repeating pattern we want looks like this:

You can produce this pattern using the Integer Divide function, which you can either initiate using custom M, or via the UI in the Add Column menu:

To get the pattern to work out exactly right, you need to subtract 1 from the Day Number and then add 1 back to the result (to number your weeks 1-5 instead of 0-4). You can do this through editing the automatically-created UI code in the formula bar as such:

or if you don’t feel comfortable doing that, creating helper columns through the Subtract and Add options on the same menu as above.

Once you’ve got WeekCycle created, we now have to factor in the weights of the given rules specified above. Again, you can do this either through custom M code in the Advanced Editor or via the Conditional Column option in the UI.

Per the hint I provided, I think the easiest way to do this is to assign 100 points to a “base” day (one with no points either added or subtracted, and then adjust from there. Here’s how that looks in the Conditional Column view:

Note that the order very much matters here, since this operates like a DAX SWITCH(TRUE( )) construct, where PQ will exit out as soon as it hits a condition that evaluates to TRUE. Thus you want to build your conditions in the order of priority that I assigned in the rules above.

Now the heavy lifting is mainly done, and we’ve got all the key components for allocating the monthly targets.

So, we just bring the monthly targets in to our Daily Targets table using a left outer join, and linking the tables based on Year and Month as follows:

Now we need to calculate the total points assigned per month and the percentage of points assigned per day.

We can calculate the first using a straightforward GroupBy with an All Rows condition so that we can expand back to the daily granularity:

Now, we just divide the Weight for each day by the TotalWeight field we just created to get a % allocation attributable to each day. We can do this using the Value.Divide M function or accessing it via the UI on the same menu we used previously for Integer Divide:

Now we just multiply our monthly targets by that daily % and Bob’s your uncle…boom! Daily targets fully allocated according to our complex rules:

You can now connect this Daily Target table directly to your Dates table by Date, and just use the simplest possible Dax measure SUM( DailyTargets[DailyTarget]) to allocate to an more coarse granularity that you need.

The first couple of times you do it, this may seem a bit involved, but trust me, once you get comfortable with it, you’ll be able to do even the most complex allocations in just a few minutes. A GREAT technique to have in your toolbox that I believe will solve the majority of your many-to-many problems.

I hope you found this helpful.

• Brian

P.S. We’re going to give you a break on the many-to-many series for this week’s Data Modeling workout just to change things up a bit, and then will return to close out the series on using virtual relationships to solve many-to-many problems.

Data Modelling Workout 007 - Many to Many Part III - Solution.pbix (1.1 MB)

[BrianJ]

Jackie,

Well done! You took the long way around initially in basically recreating the Dates table (could simply duplicate, retain only the necessary fields and merge with the Monthly Targets to pull over the monthly values, but no harm in some additional practice… ).

You totally nailed the monthly to daily allocation, which was the key to this Workout. With that understanding, you got a powerful tool to convert nearly any many-to-many to a one-to-many.

Thanks for participating!

• Brian

[kenyatta67]

[spoiler]Finished Workout 07 without any issue

Data Modelling Workout 007 - Many to Many Part III -Kenneth.pbix (1.2 MB)

[/spoiler]