6 Replies Latest reply on Mar 4, 2016 7:09 AM by Andrew Watson

# How to count selected values in filter

Hi Together,

I have the following data frame with a filter for the different months (Jan - Feb is filtered):

Item Name
Revenue Jan
Revenue Feb
Revenue per month
Car100020001500
House50005000 ???

Now I want to calculate the revenue per month. It works fine for Car but it doesn`t work for House, because there is no revenue in Feb (must be 2500).

So my question is: How can I count the number of selected months in the filter and use this value in a calculated field to calculate the right revenue per month (even when revenue values are missing)?

Here it must be 2 (also for House).

Later I want to have only the column "Item Name" and "Revenue per Month".

I would be very happy if somebody could help me!

Thanks.

• ###### 2. Re: How to count selected values in filter

You should try using the ZN function to replace NULL (blank) value with zero. Once that is done, your average value should be correct.

• ###### 3. Re: How to count selected values in filter

Unfortunately it does not work.

The not aggregated data looks like this:

Month     Item Name      Revenue

Jan         Car                  1000

Feb         Car                  2000

Jan         House              5000

Aggregated it should look like this:

Filter with Months (Jan + Feb selected)

Item Name     Total Revenue     Revenue per Month

Car                 2500                    1500

House             5000                    2500 (here is currently displayed 5000)

I am looking forward to more help and a detailed explanation how I can fix it.

• ###### 4. Re: How to count selected values in filter

Create a calculated field like this to count the number of months, call it MonthCount: {EXCLUDE [Item Name]:COUNTD([Month])}

To calculate Rev Per Month your calculation would be SUM([Revenue)/ATTR(MonthCount)

2 of 2 people found this helpful
• ###### 5. Re: How to count selected values in filter

Thank you very much, looks very good!

But I have one additional question. What is when a new product is introduced  in February.

Than it must be divided by 1 not by 2. Do you also have a solution for that?

• ###### 6. Re: How to count selected values in filter

That's a completely different requirement and at first glance appears significantly more complex. Effectively what you're asking is:

• When a month is after the introduction month (defined by what?) and had no sales - i.e. the record for that product/month combination doesn't exist - the month should be created for the purposes of the average
• When a month is before the introduction month - i.e. the record for that product/month combination doesn't exist - the month shouldn't be created for the purposes of the average

These requirements are contradictory, you're asking to create a month in some cases and don't in other. This is challenging and, without being able to spend long thinking about solutions, you may be best to create the months that don't exist where you want them to exist in your underlying data, then doing a COUNTD at item level.

1 of 1 people found this helpful