Skip to Content
author's profile photo Former Member
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.

New Business Existing Business

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

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

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

    Hi John,

    Please try the below formula.

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.