1. Table Calculations in action
In this demo, we will see the parameters in practice while working on a similar dataset as the UK clothing business database. In this data, we also have sales and orders of various products.
Let’s explore the unique count of clients per Product category, and we will also sort the count of clients from high to low.
There are many product categories. Let’s say we would like to focus only on some of them. We can expose the Product category as a filter;
But this gives us a long list of items to choose from.
Let’s create a parameter to allow our business colleagues to work with it more efficiently.
We create a Parameter the same way we would create a Calculated field: by clicking on the drop-down arrow here Or right-clicking on any empty space in the Data Pane.
A parameter window will pop up. Suppose we only want to consider clients with more than 1K unique customers. Let’s call it; Above 1K customers.
Since the threshold is numeric, we will work with whole numbers, so we will choose the Integer data type.
We will choose a range parameter and set the min value to 1K, max to 5K, and a step of 100.
Let’s show the parameter in the canvas and remove the Filter pane;
But sliding this parameter doesn’t change anything in our view!
Let’s drop the Product Category to the filters and enable this parameter via the Condition tab.
The formula will be a simple condition of the count of unique customers, which should be greater or equal to the value of our parameter selection.
Note that the parameter appears in purple font, as opposed to the dimensions and measures, distinguishable by the orange font.
Let’s apply it. Great! Our parameter works!
Let’s see how we can adapt the title of our visualization!
We will double click on the title and change it to Products with minimum .. clients, and we will refer to the value of the parameter by going to the Insert drop-down menu and selecting our parameter.
Let’s also color the value in orange to make it more visible!
Wouldn’t it be nice to also have a count of Products visible in the current selection?
We don’t have such an option in the “Insert” drop-down menu:
But we could Count distinct Product Categories in the Detail to keep it invisible from the canvas.
Let’s see what happens if we add it then to the title.
Hmm, not correct!
Let’s test the calculation in the table.
Aha! Knowing what we do about LOD expressions, this is for sure an easy one to fix!
We need to exclude the Product category from the calculation! Let’s do that!
We will make a new calculation, “Products in selection” which will count the Product Categories excluding this dimension from the calculation.
Let’s test it!
Great! We can now place it in the detail and refer to it in the title by replacing the missing measure with the new one. Let’s test it out by playing with our slider.
Awesome!
Lastly, let’s see how we can refer to the parameter in a reference line. We’ll convert the graph to a bar chart with labels and sort the count of customers from high to low; from here on, it’s easy!
Let’s go to the Analytics tab and drop a reference line onto our chart.
We will select our parameter as a value, and that’s it! We now have a parameter-operated reference line.
Let’s hand it over to you for the exercises.
2. Let's practice!