Dynamic Aggregation based on Field

[cfountain72]

Hello, 

I am trying to develop an expression that will aggregate based on a field value. Some of the dimensions should be averages, while other should be sums. I'm trying to do something like this, but I'm getting nulls.

=$(=Only(CalcType))(Measure)

where CalcType is a Field that is either 'Sum' or 'Avg' or 'Median', etc. which would yield Sum(Measure), or Avg(Measure), etc. (I could use If's but I worry about the performance implications as the data grows)

Any suggestions? 

Thanks in advance for your help,

Chris

[Sayed_Mannan · Accepted solution]

instead of if you can use PICK and MATCH functions to achieve something like this:

here is the sample you can use:

Pick(
Match(Only(CalcType), 'Sum', 'Avg', 'Median'),
Sum(Measure),
Avg(Measure),
Median(Measure)
)

I hope this helps.

[rwunderlich]

If CalcType varies, you will need to use an If() in this case. 

-Rob