I know LOD is not the favorite topic for everyone. In the last Blog post I explained my approach, which helped me the last time to figure out how to build LOD calculation in general and to recalculate the average line in particular.

Here is a short review. For an average line we need two calculations: one for the inner LOD and one the outer LOD. Before you calculate them, you need to figure out what do you calculate first:

1.The inner LOD: define all the dimensions which made up each element in the view. If you have a bar chart, which does each bar represent (For the last example, each bar is the Sum of Sales for each Sub-Category, so there is only dimension Sub-Category). If you have a scatter plot, which is each dor present for?

2.The outer LOD: define the level/the view in which we take the average of the elements

Using this approach today we are looking at a more complicated example. I find this Workout Wednesday (WOW1018 Week 38) is a great example where we can reproduce the average lines by using LOD, because it has so many components in it. But we could go through it step-by-step using the approach above.

Let’s first analyze the view we are seeing. Applying the current parameters, we see:

- 3 rows, each one is each segment

- 4 columns, each one is each year

- That made together 12 squares. In each square there are 17 dots representing 17 Sub-category.

- Y-axis and X-axis are Profit and Sales representatively.

This first step helps you to find the right components for LOD. Now we can start right away.

1.The inner LOD: define all the dimensions which made up each element in the view.

Description: Each dot represent the Sum of Sales and Sum of Profit for each Category in each Segment in each Year.

Dimensions: Sub-Category, Year of Order Date and Segment

Measures: Sales and Profit

Inner LOD for Sales and Profit seperately:

(Script: for each Sub-Category in each Segment in each Year, give me the SUM of Sales/Profit. I also like to formulate it this way: for each combination of Segment, Year, and Sub-Category. It helps me to figure out my LOD more clearly in my head.)

2.The outer LOD: define the level/the view in which we take the average of the elements

The Average here is calculated per pane. Per pane here, means for each segment in each year. So we have to fix (group by) both Segment and Year in the outer LOD.

(Script: for each Segment in each Year (one square in the view), give the the average of all the dots (inner LOD)).

Done! Now if I put in new reference lines showing the new calculated values, they just lay right at the Average Lines for both Sales and Profit. The values are to make sure that they are actually the same.