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

[gand3rson]

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

I am using three tables (one has sales by item in units [sales and cogs], the second has inventory on hand by item in units as well related cost [inventory]. As both these tables have the same item repeated multiple times for different locations, I created a third table of UNIQUE items [item list]. This third table is used to relate the first two tables so that DAX function made be done.


In the third table I calculate "months of sale" at the item level using a DAX as follows (this is actually a simplified version of the formula - the full version adjusts it for months the items was actually avalible for sale):

MOS = sum('Inventory'[On Hand Units])/sum('Sales and COGS '[Sales units])*12

So far this works great. Now that I know what my MOS is i want to put the results into "bands" (discretization in BI circles or so I am told).

I have used this Dax formula

Turn Band =if('Sales and COGS '[MOS]<=3,"0-3",if('Sales and COGS [MOS]<=6,"3-6",if('Sales and COGS [MOS]<=9,"6-9",if('Sales and COGS '[MOS]<=12,"9-12",if('Sales and COGS '[MOS]<=18,"12-18","24+"))))))


However while this works I would rather use a formula that links to a 4rth table in power pivot [bands]. That way it is easier to change the bands by changing a table as opposed to a formula.

The table looks like this

[TABLE="width: 114"]
<colgroup><col style="width: 66pt; mso-width-source: userset; mso-width-alt: 3128;" width="88"> <col style="width: 48pt;" width="64"> <tbody>[TR]
[TD="width: 88"]BandName[/TD]
[TD="width: 64"]Turn[/TD]
[/TR]
[TR]
[TD]0 - 3[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]3 - 6[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD]6 - 9[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]9 - 12[/TD]
[TD="bgcolor: transparent, align: right"]9[/TD]
[/TR]
[TR]
[TD]12 - 18[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]18 - 24[/TD]
[TD="bgcolor: transparent, align: right"]18[/TD]
[/TR]
[TR]
[TD]24 + [/TD]
[TD="align: right"]24[/TD]
[/TR]
</tbody>[/TABLE]

The DAX formula I used to try and come up with the bands for use in the [item] table is this

Turn band =CALCULATE(VALUES('Turn Bands'[BandName]),FILTER('Turn Bands','Sales and COGS '[MOS]>=min('Turn Bands'[Turn])&&'Sales and COGS '[MOS]<max('Turn Bands'[Turn])))

When I use that formula I get this error

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

I admit I am hacking these DAXs together based on internet research so If anyone can help I would be most grateful

 

[Laurent C]

The problem comes from CALCULATE(VALUES('Turn Bands'[BandName]) ...

CALCULATE expects a scalar expression (an expression return a single value). VALUES actually returns a column expression. If this column only contains 1 scalar value, then the expression will work fine.

Now if you have more than 1 value in your filter context (like in subtotals), then the calcualation will break.

So, you may actually want to rewrite your calculation taking multiple selections into account, or add a safeguard expression like IF(COUNTROWS(VALUES(...)) = 1, ...

 

[JavierGuillen]

The best way to 'band' is to specify, in your lookup table, the min and max value. In other words:

BandName Min Max
0-3 0 3
4-6 4 6

and so on.

You DAX formula should then be:

CALCULATE(VALUES('Turn Bands'[Band Name]), FILTER('Turn Bands', 'Sales and COGS'[MOS] >= 'Turn Bands'[Min] && 'Sales and COGS'[MOS] <= 'Turn Bands'[Max]) )

 

[gand3rson]

Javier Guillen fine Sir you are a gentleman and a scholar

i was close but obv messed the min max thing up. All I had to do was remove one of your = signes from the two >= <= and it worked perfectly.


Thank you

 

[JavierGuillen]

Cool. Glad it helped!