A table of multiple values was supplied where a single value was expected

[Anonymous]

I have a table which has the budget for each area by month. 

I'm using a calculated column to calculate the daily budget by dividing the monthly budget for the respective area by the no. of days in a month. I'm using this formula to calculate this:

DailyBudgetArea = DIVIDE(

LOOKUPVALUE('Budget by Area'[Budget],

'Budget by Area'[Month], FORMAT(DailyBudgetArea[Date], "mmm"),

'Budget by Area'[Area],DailyBudgetArea[Area]),

DAY(EOMONTH(DailyBudgetArea[Date],0)))

It works fine when I have budget for only the year 2019 but when I add in the values for the 2020 it gives the error: A table of multiple values was supplied where a single value was expected. I understand why this is happening but I'm not sure how to correct this. 

Any help on this would be really appreciated. Thanks! 

[Ashish_Mathur · Accepted solution]

Hi,

Does this work?

DailyBudgetArea = DIVIDE(LOOKUPVALUE('Budget by Area'[Budget],'Budget by Area'[Month],FORMAT(DailyBudgetArea[Date],"mmm"),'Budget by Area'[Year],YEAR(DailyBudgetArea[Date]),

'Budget by Area'[Area],DailyBudgetArea[Area]),DAY(EOMONTH(DailyBudgetArea[Date],0)))

The data type of Year column on the Budget by Area tab should be numeric.

[Anonymous]

Hi @Ashish_Mathur ,

@amitchandak  Was able to provide with me a solution that works.

I also tried your formula and it works perfectly. 

Thanks everyone for your help!

[v-eachen-msft]

Hi @Anonymous ,

Your current conditions in LOOKUPVALUE() function are not enough to match a unique value. You could use SUM() function and filter with your conditions to sum values in the same month.

[amitchandak]

Check this solution how to convert yearly budget into monthly

https://www.dropbox.com/s/tz74khz4m5iyq0q/anuualMonthlyTarget.pbix?dl=0

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[Anonymous]

Hi @amitchandak ,

I already have a monthly budget. I need to convert that into a  daily budget.

[amitchandak]

In the same file, I added a summarized daily target. Check out at :https://www.dropbox.com/s/tz74khz4m5iyq0q/anuualMonthlyTarget.pbix?dl=0