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

[Anonymous]

Hi,

I am stuck while creating a visual using DIM_DATE['% of Tracking]. I am getting this error. Please help me out how to fix this.

The relationship between my two tables is :

where Query 2 is coming from sql and DIM Date is in excel file and rest are measure in DIM TABLE

Business days:

GP: CALCULATE(SUMX(RELATEDTABLE(Query2),Query2[GP]))

Revenue: CALCULATE(SUMX(RELATEDTABLE(Query2),Query2[merch]))

Running business days : 

CALCULATE

(

[Business days],

FILTER(ALL('DIM_DATE'),

DIM_DATE[YEAR] = MAX(DIM_DATE[YEAR]) &&

DIM_DATE[MONTH] = MAX(DIM_DATE[MONTH]) &&

DIM_DATE[DAY] <= MAX(DIM_DATE[DAY])

))

Running Total GP : 

CALCULATE

(

[GP],

FILTER(ALL('DIM_DATE'),

DIM_DATE[YEAR] = MAX(DIM_DATE[YEAR]) &&

DIM_DATE[MONTH] = MAX(DIM_DATE[MONTH]) &&

DIM_DATE[DAY] <= MAX(DIM_DATE[DAY]))

)

Total business days of month : CALCULATE

(

[Business days],

FILTER(ALL('DIM_DATE'),

DIM_DATE[YEAR] = MAX(DIM_DATE[YEAR]) &&

DIM_DATE[MONTH] = MAX(DIM_DATE[MONTH])

))

Daily Tracking : [Running Total GP]/[Running business days]*[Total business days of month]

% of Tracking : DIM_DATE[Daily Tracking]/VALUES(Query2[Quota])

 I get error when i pull % of tracking in my visual.

[parry2k · Accepted solution]

@Anonymous VALUES is going to return unique value for quota, you can use MAX or some other aggregated function

% of Tracking : DIM_DATE[Daily Tracking]/MAX(Query2[Quota])