Calculated field that uses the same metric twice but ignores the filter for one of them

Q&A
, , ,
1

I have a Share of Sales metric where the numerator is the total Sales but constrained to certain categories, and the denominator is also total Sales but not constrained by category. Formula below:

sum(ifelse({category} <> ‘All Others’, {Sales}, 0)) / sum({Sales})

This works how I want it to, however I also want to have filter on ‘category’. When I put the filter on category and select a certain category (let’s call it ‘Big Accounts‘), the Share of Sales ends up being 100% because both the numerator and denominator are now constrained to Big Accounts.

Any suggestions?

2

Hi @Jordan_Hill,

Take a look at use case #4 in this article:

Level-Aware Calculations: Tips and Best Practices

3

@David_Wong Thank you. I realize that I actually want some of the filters to work, and others to not. For example, I want the country filter to apply to both the denominator and numerator, but I only want the category filter to apply to the numerator. I’m trying this, but it’s not quite working:

sum({Sales}) / min(sumOver({Sales}, [country], PRE_FILTER))

This works when I have one country selected in the country filter, but it does not work when I have multiple countries selected. It doesn’t seem to be related to whether I use min or max. Any advice?

4

@David_Wong checking in on this. Are you able to suggest a path forward here?

5

Can someone please look into this?

6

Hi @Jordan_Hill,

Sorry for the delay. Can you try this?

sumIf({Sales}, in({country}, ${country})) / min(sumOver(ifelse(in({country}, ${country}), {Sales}, null), [], PRE_FILTER))

You’ll need to use a parameter. Create a country parameter and link it to your country filter. Then add a control for the parameter.

7

@David_Wong getting very close! The only problem now is that this only works if the country parameter has a default value. However I want all countries to be the default selection. I’ve tried adding ‘ALL_VALUES’ as the default value and incorporating that into the logic, but it’s still not working. Any thoughts?

8

@Jordan_Hill You can use a parameter in a calculated field only if it has a default value.

I’ve also run into issues using ‘ALL_VALUES’ in the past. I think the best you can do is set the default to some dummy value like “Select a country” or set it to blank.

image
image1154×602 23.3 KB

9

Hi @Jordan_Hill, the string ‘ALL_VALUES’ is a reserved value, but you can check if the parameter is “All” using in(NULL, ${country}). You can then incorporate that into the logic of the calculated field, such as:

ifelse(
    in(NULL, ${country}),
    sum({Sales})/min(sumOver({Sales}, [], PRE_FILTER)),
    sumIf({Sales}, in(country, ${country}))/min(sumOver(ifelse(in(country, ${country}), {Sales}, null), [], PRE_FILTER))
)
10

I’m still not able to do this. I think I’ll just do without at this pont.

11

Hi @Jordan_Hill,

Following up here as it’s been a while since last communication took place on this thread; did you have any additional questions regarding your initial post?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you

12

Hi @Jordan_Hill,

Since I have not heard back further, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community.

Thank you