Hi everyone, welcome back to my blog. Today we had an extensive training session on Level of Detail Calculations (LOD). We are shown the difference between FIXED, INCLUDE, AND EXCLUDE and when to use them. I was not quite comfortable with LOD and avoided them before. However, I am more comfortable with LODs after today’s session. I developed a good relationship with LOD and started to favor them over table calculation. The reason is that I have more control over LOD compared to the counterpart when it comes to filtering. Let me show you the cool things that I’ve learned today by building a chart that uses nested LODs showing the min and max values for sales over the entire monthly sales. Less talk and start building our chart now.

          1. Computing a Monthly Sales Chart that highlights the selected region

Create Region parameter

Drag [ Order Date ] to Columns, Left-Click, then choose continuous Month

Drag [ Sales ] into Rows and [ Regions ] into Detail.

Create a calculated field in order to highlight our selected Region

Selected Region vs. others

IF [Region] = [Select a Region] Then [Region] else ‘Others’ END

Drag the newly calculated field into Colour

At this stage, we have a chart that highlights our selected Region. Next, we need to create Max and Min Sales Value that are presented by dots with contrasting colours.

          2. Creating Circles that represent Max and Min Sales Value

For this step, we need to create our nested LOD calculations as below

Monthly Sales by Region

{FIXED [ Region ], DATETRUNC( ‘month’, [ Order Date ]): SUM( [ Sales ] )}

// Sales by Region and Month

 

MIN Regional Sales

{EXCLUDE DATETRUNC( ‘month’, [ Order Date ]) : MIN([ Monthly Sales by Region ])}

// looking at the Regional Monthly Sales and return the minimum sales value

 

MAX Regional Sales

{EXCLUDE DATETRUNC( ‘month’, [ Order Date ]) : MAX([ Monthly Sales by Region ])}

// looking at the Regional Monthly Sales and return the maximum sales value

 

[MIN/MAX Regional Sales]

IF SUM ( IF [ Region ] = [ Select a Region ] THEN [ Sales ] END ) = ATTR ( [ MIN Regional Sales ] ) OR SUM ( IF [ Region ] = [ Select a Region ] THEN [ Sales ] END ) = ATTR ( [ MAX Regional Sales ] THEN SUM ( [ Sales ] ) END

// For every selected region only, returning the maximum and minimum values

// This calculation will just return the maximum and minimum values only in order to compute the two dots.          

 

Drag the [ MIN/MAX Regional Sales ] into Rows

Click on [ Selected Region ] then changes to Detail and drag [ Sales ] into Colour.

          3. Edit colour and Formatting

Click on Colour Legend -> Edit Colours -> Custom Diverging under the Palette dropdown.

Finally, make a Dual Axis then Synchronise them all together.

I hope you enjoy my tutorial and find it useful. Thank you for following along.

 

Quyen Ha
Author: Quyen Ha