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

[ilovasen]

Hi

I am trying to use make a pivottable per month and year based on the formulas below.

Gross Sales USD:=CALCULATE(sum(factFin[AmountUSD]), ALL( DateTable), FILTER( DateTable, DateTable[MonthOfYear] = VALUES( DateTable[MonthOfYear])), FILTER(ALL(CoA),CoA[AccSum2] = "398X"))

Gross Sales GWh:=CALCULATE(sum(factFin[belopp]), FILTER( DateTable, DateTable[MonthOfYear] = VALUES( DateTable[MonthOfYear])), FILTER(CoA,CoA[AccSum2] = "E-SOLD-SUM"))

Monomic Price Cent Per kWh:=DIVIDE(([Gross Sales USD]*1000*100),([Gross Sales GWh]*1000000),BLANK())

I am able to get the individual results if I select a month from a Timeline slicer. Why should the error message show up and how can i solve it.

Any suggestions?

brgds,
Inge

 

[ilovasen]

Problem solved by removing , FILTER( DateTable, DateTable[MonthOfYear] = VALUES( DateTable[MonthOfYear]))

Brgds, Inge

 

[scottsen]

Likely what is happening is that on the Grand Total cell, you have multiple MonthOfYear, so the VALUES() function gets grumpy. You can prevent that by wrapping your measure in IF (HASONEVALUE(DateTable[MonthOfYear]), ...)