It doesn't matter what data set you use on this one. Today we're talking about a concept and a trick that makes the concept easier. Here's the deal. I've been doing some work recently where I have to make a table that contains a bunch of different metrics. Problem is they all have different filters and look at different fields (and again they all have to be on the same table). Given this set up, using the Filters shelf isn't going to work as it filters everything the same way. Therefore, you're going to have code in the filters into the calculated field into the measure. Stop me if you've heard this on before.
So as I write these equations, I need to know what to filter - ie what's in the data, and that's where our trick comes into play. Here's the example. This particular metric (the one I'm making up as I type) requires I divide Furniture sales in the West Region by the number of Technology units sold in the East Region. So what does that look like as a calculated field?
Create a calculated field. We'll call it "Weird Average" and we're going to write an if statement that has booleans (T/F) inside of the "if" part and a measure in the "then" part of the statement. Because we're looking at two different numbers, we'll have to do the if/then twice, and aggregate each one.
As I begin to write this out, I forget what's actually inside of "Region" - is it West or Western? And here's the trick - there's a cute little button called "Describe..."
When you click it, you see a bunch of information, but you don't immediately see anything that's helpful. Then there's one more magical button called "Load".
I hit that and here's the awesome thing - now I can see exactly what's inside of this data field. Turns out it is called "West" and not "Western". I can select it, hit the copy button, close out and paste it into my formula.
And this is what it looks like:
This is really helpful for spelling and abbreviations that might be in your data. I discovered this a few weeks ago, and now I'm using it constantly. Here's what that full formula looks like (remember - I divide Furniture sales in the West Region by the number of Technology units sold in the East Region):
Ta-da! Fun with metrics! Hope you enjoyed!
Many thanks -