How to resolve the Error: A table of multiple values was supplied where a single value was expected

[saqib1]

Hi,

I have seen other posts regarding this error also, but none of them could resolve the issue in my case, so here goes:

A certain product is produced through 5 processes. Depending upon the quality of finished goods required, the raw material can be used in low, normal, or high quantities. Based on a slicer for selecting the RM input level and a separate slicer for units produced of the product, the total raw material consumed to produce X products with low/normal/high raw material to be selected through slicer is required. 

Input Level	Process	RM in Kgs
Low	1	10
Low	2	12
Low	3	14
Low	4	16
Low	5	18
Normal	1	12
Normal	2	14
Normal	3	16
Normal	4	18
Normal	5	20
High	1	14
High	2	16
High	3	18
High	4	20
High	5	22

Example: If "Low" RM Input level is selected, then each row of the low category should get multiplied with the selected units produced and divided by 50000 to get converted into bags.

If 5,000 units are selected and input level is low, answer should be = 7 bags

As seen in the screenshot, the DAX:

Total RM Consumed =

var a = CALCULATE(VALUES('RM Input Level'[RM in Kgs]),

 FILTER('RM Input Level','RM Input Level'[Input Level]= SELECTEDVALUE('RM Input Level'[Input Level])))  

RETURN

     SUMX('RM Input Level', a* 'Units Produced'[Units Produced Value])/ 50000

(Need to divide by 50000 to convert the RM into bags) 

is giving error : A table of multiple values was supplied where a single value was expected.

kindly guide how to resolve this issue.

https://1drv.ms/u/s!AoqwAyWfotQsjmRi1tAHunMH0nH3?e=zR3AVp 

[ThxAlot · Accepted solution]

[saqib1]

@ThxAlot In the same data above, the units produced selection needs to be aggregated everytime in each subsequent process and then get multiplied by the RM. For example, if "Input level" is "low" and "Units produced" selected are 5000, then:

10*5000 + 12*10000 + 14*15000 + 16*20000 + 18*20000.

Can you kindly guide how can the DAX be modified for this?

[ThxAlot]

Glad to offer further help; but what's the logic for 10000, 15000, etc. ?

[saqib1]

Actually this is somewhat different scenario...the data is same as in the example given but number of units produced are taken to be aggregating with each process. i.e. In process 1, (if 5000 units selected) then 5000, in process 2, 10000, in process 3, 15000 and so on..

[saqib1]

Thanks Alot @ThxAlot 🙂  the DAX is working exactly as intended but can you kindly guide how does the formula select the input level even though there's no mention of it in the DAX?

[ThxAlot]

Aha, there's a mysterious power called "filtering" in the world of DAX; it's everywhere in any possible forms like slicer, column/row in table/matrix, legend of other visuals, etc.

Seriously, "filtering" is the essence of DAX. You don't need to explicitly refer to certain table/column in a measure as they already function through certain slicer/relationship. The more practise, the more elegant you'll find it.

[saqib1]

Lol 😁 Thanks for guiding as well as for the humour.

[some_bih]

Hi @saqib1 

try version v2 below

Total RM Consumed_v2 =

var new_x=SELECTEDVALUE('RM Input Level'[Input Level])))

var a = CALCULATE(VALUES('RM Input Level'[RM in Kgs]),
FILTER('RM Input Level','RM Input Level'[Input Level]= new_x

RETURN
SUMX('RM Input Level', a* 'Units Produced'[Units Produced Value])/ 50000

[saqib1]

Its still giving the same erorr.