cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
droupa Frequent Visitor
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

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 

 

property.png

 

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

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 

droupa Frequent Visitor
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!

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

 

 

droupa Frequent Visitor
Frequent Visitor

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

Ok. here it is: Sales Report

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 

 

property.png

 

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

droupa Frequent Visitor
Frequent Visitor

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

Ah! makes sense now. thanks!

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

your welcome

 

If there is nothing further please mark the topic as solved

droupa Frequent Visitor
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?

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

ill take a look now