Your cart is empty now.
Love to learn from video tutorials instead? Here you go!
Introduction with Example
Say, for example, we have a Sales datasets with the following fields:
Transaction ID: Unique identifier for each sale transaction
Product Name: Item name.
Product Category: The higher category label for the product name.
If we were to calculate the total stationery orders, we have to count the total number of orders (identifiable by distinct transaction ID) with the rule of
product category = "Stationery".
First Attempt of the Combo Formula
Well, I believe many would encounter this error like me:
IF(Category = 'Stationery',COUNT_DISTINCT(Transaction ID),0)
Unfortunately, this is not the correct solution as we have violated the rule that metrics and dimensions cannot co-exist in the same formula.
Hence, I’m getting the error below:
So, what is the reason that the formula getting rejected?
Let’s investigate further.
- First of all, from the “surface” level, it seems that we only used 2 dimension fields:
- This can be further proven by the screenshot below where both fields are coloured in green. Side Note: Aggregated metrics are highlighted in blue colour instead.
- What we missed out here is the formula
COUNT_DISTINCT(). This is an aggregation function that returns an aggregated metrics.
In short, if the returned result from a formula is an aggregated metric, the formula is said to have used aggregated metrics.
Therefore, it explains the error message we getting where it says we should not mix aggregated metrics with dimensions.
How to fix this?
My solution is to use conditional formula (i.e.
CASE) to derive the unique
Transaction ID as a new field. Then, we count the total number of unique transaction ID in the new field.
Here are 2 sample formulas:
COUNT_DISTINCT(IF(Category="Stationery", Transaction ID,NULL))
COUNT_DISTINCT(CASE WHEN Category="Stationery" THEN Transaction ID ELSE NULL END)
To explain in detail the steps involved:
- First, we need to use
CASE WHENfunction to define the rules.
- Make sure that when the condition is
TRUE, you returned the unique identifier as the value (In this case, the unique identifier for Sales dataset is
NULLwhen the condition is FALSE. Don’t return 0 or any other values; Otherwise, the distinct count would be inaccurate.
- Wrap the
CASE WHEN()statement with
The above Dashboard screenshot clearly shows that total sales for stationery (as shown in a scorecard) is at 6, which is consistent with what we get from the raw transaction table.
Other than the calculated field method in this article, we can actually use filter and scorecard to achieve the same result.
In this short 3 min video below, I covered how you could calculate and show average duration in scorecards using 2 different ways, one of it is by using a filter control within the chart. If you want to explore and understand more, click the video below to learn!