Former Member

# #Multivalue error

Hi,

I have a dataset of customers (with unique customer numbers) that have sales in 2 years. 2012 & 2013. I have added a flag to each customer
to identify customers which are new business customers in 2013 (i.e. the customer didn't have sales in 2012) and existing business customers (i.e. had sales in 2012).

I have created a dimension to put the flag on each customer. The formula I'm using is as follows:

=If(IsNull([Sales Quantity Full Year] In ([Customer No]));0;1)

where,

Customer No is the unique customer number

Sales Quantity is total sales in the prior year

New business customers will have a flag of 0

Existing business customers will have a flag of 1

All this works fine, no issues so far.

Customer sales are broken out into different sales areas, North, South, East & West.

What I want to be able to show is a vertical table with the sales areas in a vertial column on the left, then beside that the total for new business sales in the period (2013) and then another column for exisitng business sales in the period. I don't want to show customer numbers as its too much detail.

North 1,000 12,000

South 2,000 11,000

East 500 8,000

West 5,000 15,000

But when the try to show the table like this I get #Multivalues where the values should be.

If I only include North and also include the customer numbers the values are shown, when I remove the customer number and so at an regional level, I get #Multivalues again.

I have created a measure for New Business, the formula reads as follows:

=Sum([Sales Quantity 2013]) Where ([New Customer]=0)

The formula for Exisiting business is the sames only 0/zero is replaced with 1.

Hope someone can help,

Thanks,

John

10|10000 characters needed characters exceeded

### Related questions

• Posted on Jan 04, 2014 at 04:20 AM

Hi John,

if the table contins only [Sales Area] dimension use below formula:

=Sum([Sales Quantity 2013] Where ([New Customer]=0))

or

if the table contins more dimensions in addition to [Sales Area] dimension use below formula:

=Sum([Sales Quantity 2013] Where ([New Customer]=0)) IN ([Sales Area])

Hope this helps..

Thanks,

Bala