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

[droupa]

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?

[AnthonyTilley]

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]

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

Thanks!

[AnthonyTilley]

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]

Ok. here it is: Sales Report

[AnthonyTilley · Accepted solution]

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

[droupa]

Ah! makes sense now. thanks!

[AnthonyTilley]

your welcome

If there is nothing further please mark the topic as solved

[droupa]

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?

[AnthonyTilley]

ill take a look now 

[AnthonyTilley]

okay i have created a table that is a distinct list of proporties in your budget table 

i have then changed you Daily targets table to be a combine of your calander and this table to create a row for each merged item in the table 

i have then changed your concat formulas to give a unique refrence for the month year colunm and changed the relationship 

i have then fixed your lookup colunm in the daily targets table 

hope this helps 

pbix file

[droupa]

Thanks Anthony! This has really helped.

I added a divide function to my lookup formula, and now I have blanks in my DAILY TARGET Column in my DAILY TARGETS Table, and I am not seeing any logical reason why? Would you know?

Sales Report 2