Today’s tip involves two handy little tricks I learnt whilst teaching Level of Detail (LOD’s) to DS2.
So the scenario is- you want to see the average sales by month and year but we also want to define the average sales specifically per year, which then can enable you to see which monthly average sales are above or below the yearly average sales. Sounds complicated right? Let me break it down in simple steps.
Let’s create the first view, drag AVG(sales) to columns, discrete year(order date) to rows and discrete month(order date) also to rows. So this view shows the average sales per month per year.
Now if we want to find just the year’s average then we need an LOD calc. However we cannot use Year(Order Date) in an LOD calc. So we have to create a custom date. Go to order date, click Jeremy (Triangle), create, custom date, change detail to years. This now creates a dimension that can be used in a calculated field.
Now lets create an LOD calc that just focuses on the average yearly sales. This equation is fixing the average sales based on the year custom date.
If we put this new calculated field onto detail, and create a reference line per pane you can see the average sales per year for each year.
Just to make the chart more visual, we can create a final calculated field that shows the months that are above or below the average yearly sales.
Then drag this new calculated field onto colour and you can see the months that were above or below the average sales for that year
I tried to use a real world situation. If you have any problems or need a different scenario to work from please let me know!
Thanks for reading