Pick or Match using a Variable to Dimension Value to record averages

[swetasingh]

Hi, 

I am presently loaded a table via script which has ID , Band and Band Value . Something like this : 

Model ID	band	band value
AB1	0.2	1.178
AB2	0.25	2.364
AB1	0.25	-1.39
AB2	0.2	0.009
AB1	0.75	-2.333
AB2	0.75	0

I have also created a variable(not in script), where when user inputs certain value, the variable will calculate and give value as in band. 

My problem is : Let say the variable after user input returns the value of 0.25. 

How do i match 0.25 in the above table , to bring the band value of 0.25 which is 1.178. I want to show this in a table.

I tried using below formulas but getting no results and no error as well : 

if(band = $vBandCal, Bandvalue)

if(match(band, $vBandCal, avg(Bandvalue))

Please let me know where am i going wrong? 

[asinha1991]

I am not sure if I understand everything, but if you want to filter within aggregation, this should be the way

avg(if(match(band, $vBandCal, Bandvalue),Bandvalue))

It will be better if you share more details about input and output..

For example I see two model with same band(0.75), what do you want to show in that case

[swetasingh]

Hi, Thanks!

There are 2 basically 2 models with model IDs where band is same across all the model but only band values differ. 

The variable $Band Cal calculates the co-eff using user input which is on the fly and is equal to the dimension band.

I tried using the suggested solution but it gives out of memory error. 

Basically the output would look like this : 

Model ID	$Band Cal	Band Value
AB1	0.25	-1.39
AB2	0.75	0

[asinha1991 · Accepted solution]

Correction
avg(if(match(band, $vBandCal),Bandvalue))

[swetasingh]

Hi, the problem is with variable declaration. I fixed that and it worked using below formula!. Thanks!