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

[MizeelA]

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

Michael

[MizeelA]

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

[TomMartens]

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

[MizeelA]

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).Correct resultsincorrect results

[TomMartens]

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

[MizeelA]

Hi Tom,

Thanks for you quick response.

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

Regards

Michael

[MizeelA]

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