SSRS Tablix Cell Calculation based on RowGroup Value -

i have looked through several of posts on ssrs tablix expressions , can't find answer particular issue.

i have dashboard creating contains summary data various managers. entering monthly summary data single table structured this:

create table operationmetrics date date plant char(10) sales float returnedproduct float 

the data use grouping created table referencing report group these metrics go looks this:

create table operationsreport reporttype varchar(50) metrictype varchar(50) 

in table, 'sales' , 'returnedproduct' metric column, while 'execsummary' or 'quality' reporttype entries. join, decided unpivot operationmetrics table...

select date, plant, metric, metrictype  (select date, plant, sales, returnedproduct operationmetrics) unpviot (metric metrictype in (sales, returnedproduct) unpvt 

and join operationsreport table have grouped metrics.

select date, plant, metric, rpt.metricreport, metrictype opmetrics_unpivoted opex  inner join operationsreport rpt on opex.metrictype = rpt.metrictype  

(i understand elements of not ideal not in control of our destiny.)

this not include whole of tables gist. so, have form fill in operationmetrics table. chose ssrs display output.

i created tablix following configuration (i can't post images due rep...)

date column group, grouped on 'mmm-yy' parent row group reporttype child row group metrictype

now, problem of metrics calculations of other metrics. instance, 'returned product (% of sales)' not entered manager because assumed can calculate that. returnedproduct divided sales.

i attempted calculate using lookup function, below:

switch(fields!friendlyname.value="sales",sum(fields!metric.value), fields!friendlyname.value="returnedproduct",sum(fields!metric.value), fields!friendlyname.value="returnedproductpercent",lookup("returnedproduct", fields!friendlyname.value,fields!metric.value,"metricdataset")/ lookup("sales",fields!friendlyname.value,fields!metric.value, "metricdataset")) 

this works great! first month... since lookup looks first match, posts same value rest of months after.

i attempted use this got me @ beginning since dataset not have value.

any received. keep rowgroup hierarchy.

it sounds lookup working need include date find right month. lookup return first match why it's working on first month.

what can try concatenating metric name , date fields in lookup.

lookup("sales" & cstr(fields!date.value), fields!friendlyname.value & cstr(fields!date.value), fields!metric.value, "metricdataset") 

let me know if misunderstood issue.