cancel
Showing results for
Did you mean:
Frequent Visitor

## lookupvalue - "A Table of Multiple values was supplied where a single value was expected"

Hi,

I have a Budget table that has a budgeted amount per month of the year.

I am trying to lookup a budget value (table: Budget) for a month to another date table (table: Daily Targets), to calculate a daily budget value. (My budget table only has monthly targets, but I need to obtain daily targets by dividing this value by the number of days)

In my Budget table, I created a column to work out the number of days for that month using:

DAYS IN MONTH = EOMONTH([DATE],0)-[Date]+1

I also created a "Month and Year" text Column using:
Month and Year = concatenate(month(Budget[Date]),year(Budget[Date]) )

The Budget table therefore has a unique reference for each row for month and year.

In my Daily Target table, I created a column for Month and Year the same way:
Month and Year = concatenate(month('Daily Targets'[Date]),year('Daily Targets'[Date]) )

Therefore, each day has a month and year that it relates to; All days in the same month will have the same reference.

I want to lookup a column from my Budget Table using the Month and Year, using:
Daily Target = lookupvalue(Budget[Revenue (Budget)],Budget[Month and Year],'Daily Targets'[Month and Year])

I receive the error: "A table of multiple values was supplied where a single value was expected"

I dont understand why, because the Budget table has unique values; Why cant the formula just return the column value using the unique reference?

1 ACCEPTED SOLUTION

Accepted Solutions
Member

## Re: lookupvalue - "A Table of Multiple values was supplied where a single value was expected&am

your month year filed in the budget table is not unique because there are 4 entries for every Month one fore each of your Propertys

if you have the Propert in the Daily targets then concat all thre month year and propert to create a unique ref

if you do not have this filed then you need to find another way to make a uniqe ref

11 REPLIES 11
Member

## Re: lookupvalue - "A Table of Multiple values was supplied where a single value was expected&qu

Can you provide a PBIX file so that i can review the data,

this error would imply that your unique refrence colunm of month year is not unique

if you upload a file i can review this and try an find the error

Frequent Visitor

## Re: lookupvalue - "A Table of Multiple values was supplied where a single value was expected&am

@AnthonyTilley Sorry, I am quite new; Can you tell me the steps to upload the PBIX file and I will do so?

Thanks!

Member

## Re: lookupvalue - "A Table of Multiple values was supplied where a single value was expected&am

ypu will need to save the PBIX file to a cloud storage such as onedrive, google drive or drop box and then share the file to public with a link and then post the link in this thread

Frequent Visitor

## Re: lookupvalue - "A Table of Multiple values was supplied where a single value was expected&am

Ok. here it is: Sales Report

Member

## Re: lookupvalue - "A Table of Multiple values was supplied where a single value was expected&am

your month year filed in the budget table is not unique because there are 4 entries for every Month one fore each of your Propertys

if you have the Propert in the Daily targets then concat all thre month year and propert to create a unique ref

if you do not have this filed then you need to find another way to make a uniqe ref

Frequent Visitor

## Re: lookupvalue - "A Table of Multiple values was supplied where a single value was expected&am

Ah! makes sense now. thanks!

Member

## Re: lookupvalue - "A Table of Multiple values was supplied where a single value was expected&am

If there is nothing further please mark the topic as solved

Frequent Visitor

## Re: lookupvalue - "A Table of Multiple values was supplied where a single value was expected&am

Do you perhaps have a quick way for me to edit my Daily Target Table so that I create 4 records of each row, and include a new column stating the lodge, so that I can create my unique reference?

Member

## Re: lookupvalue - "A Table of Multiple values was supplied where a single value was expected&am

ill take a look now