LoDs of Tips & Tricks: Using LoDs to filter a viz
This is the first part of a series of blogs demonstrating practical and interesting ways to use level of detail calculations (LoDs). The aim of the series is to show how LoDs can be used to enhance a viz by making it more interactive and dynamic, and therefore more accessible to the user.
To create a really interactive and dynamic viz you often want to filter and change information being represented in the viz. Normally, you can either filter by any of your dimensions or by measures, as long as the measures are being queried in the viz. This blog is going to show you how to filter a viz by a measure that is not being queried, and how to make this filter more dynamic using an LoD.
When using filters you can either filter by dimensions; Customer Name, Category or Product, and you can also filter by measures; Sum ([Sales]), Average ([Profit]) or Count ([Item])
When filtering using a measure you are only able filter by what is being queried in the viz; adding Sum([Sales]) to the filter shelf with Category in the view gives you the range from the minimum $32,249 to the maximum $4,380,165
Whereas filtering Sum([Sales]) with Customer Name in the view gives you the range from $3 to $219,891
If you want to filter the Category Sales sheet you could use a conditional filer on Customer Name. However, you can only use a Boolean type calculation in these type of filters. E.g. Sum of sales greater/less than 100,000
What if you want to filter a sheet by a range of different values of Customer Name sales? What if you want to be able to quickly change between these categories? What if you wanted to include these categories in the viz? Here’s where LoDs come in really handy.
The trick here is; you use an LoD calculation to calculate the Sum([Sales]) aggregated at the Customer Name level, then create different sales categories and use that as a filter:
- Call the calculation LoD Customer Sales
- Fix the calculation to aggregate at the Customer Name level:
{fixed [Customer Name]
- Calculate the Sum([Sales])
{fixed [Customer Name] : Sum([Sales])}
After this we need to create another calculation that indicates the lower limit of the sum of sales:
- Call the calculation Customer Sales Categories
- Create an if statement to categorise a range of values
IF [LoD Customer Sales] >=100000 then “Customers with Higher Sales”
ElSEIF ([LoD Customer Sales]) >=50000 and ([LoD Customer Sales]) < 100000 then “Customers with Medium Sales”
ELSE “Customers with Lower Sales”
END
What you’ll notice is this calculation has now gone into the dimensions shelf, which means you can use this calculation in exactly the same way as any other dimension. You can filter, stack, partition, group, create a hierarchy, whatever!
In this case we’re going to add the calculation as a filter to the sheet visualizing sales by Category. This gives the ability to filter the viz by each category of customer sales. Taking this further you can now build different visualisations and start analyzing these customer categories in a more interactive and dynamic way
Tune in for more blogs in this series, where I’ll demonstrate other ways of using LoDs to create interactive and dynamic visualisations
