cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MizeelA Frequent Visitor
Frequent Visitor

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

Dear Forum,

 

We have built a utilisation report but when mulitiple "month year" filter is selected it goes blank but is okay when a month is selected. Don't know what is causing the issue? 

 

I have attached the file which contains all the data.

 

Regards

MichaelPIC 1.PNG

6 REPLIES 6
MizeelA Frequent Visitor
Frequent Visitor

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

To add more infomation. I am looking the total number of days within the month from another table using ths formulae 

1.lookupvalue =
CALCULATE(
FIRSTNONBLANK('Table'[Hours_available_per_Month],1),
FILTER(
ALL('Table'),
'Table'[Month year]= Delivery_TS[Month year]))
 
Then using the results to find the % utilised
1.Period% = DIVIDE(SUM(Delivery_TS[duration]),DISTINCT(Delivery_TS[1.lookupvalue]))
 
This will allow me to filter by the Month year 
Month year = FORMAT(Delivery_TS[timedate],"MMMM-YYYY").
 
This works if I select one month year but selecting say May 2019 and June 2019 throws it out.
 
Regards
 
Michael
Super User
Super User

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

Hey,

 

you should try to replace this:

...
FILTER( ALL('Table'), 'Table'[Month year]= Delivery_TS[Month year]))

with this

...
FILTER( ALL('Table'), 'Table'[Month year] in VALUES(Delivery_TS[Month year])))

This will avoid the error, but you have to check, if the result is what you expect.

 

Regards,

Tom

 

 

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
MizeelA Frequent Visitor
Frequent Visitor

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

Thanks Tom for your quick response really appreciated.

 

The outcome is unfortuantely different when I used the "in Values" screen grab file attached as suppose to the equal sign =. 

 

I am expecting =45.75(hours worked)/172.5(number of workings hours in a month (23days@7.5hrs).when is equal =.PNGCorrect resultswhen in Values is used.PNGincorrect results

Super User
Super User

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

Hey,

please upload your pbix to onedrive or dropbox and share the link.

I assume that your first calculation "1. lookupvalue" has to consider the multi-value selection, meaning you may have to rewrite your measure using SUMX to iterate over the selected year-month items.

Regards,
Tom
Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
MizeelA Frequent Visitor
Frequent Visitor

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

Hi Tom,

 

Thanks for you quick response.

You are totally right. I will drop the file into onedrive.

 

Regards

Michael

 

 

MizeelA Frequent Visitor
Frequent Visitor

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

Hi Tom,

 

Thanks again for your reply.

I have attached the link to onedrive below

 

https://1drv.ms/u/s!AiBdJoIsb4-2iA3PIk5cGIZyjHhx?e=WZvVUH

 

Regards

 

Mic

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 139 members 1,851 guests
Please welcome our newest community members: