Average and Aggr formulas

[mikecherry]

Hi all,

I am trying to calculate an Average in one line but, because I am filtering and at the same time avoiding that filter the AGGR formula doesn't work properly .

This is my actual table and  the expression is:

Called:

-SUM({<Data_Type={"Trans"},Fund_Name,Benchmark_Key={"$(vBenchmarkField)"}, Data_Measure={"Cost"}, Date_ID={"<=$(vReportDate)"}>} $(vMeasure))

/

SUM({<Data_Type={"Trans"},Fund_Name,Benchmark_Key={"$(vBenchmarkField)"}, Data_Measure={"Commitment"}, Date_ID={"<=$(vReportDate)"}>} $(vMeasure))

vBenchmarkField= Concat(DISTINCT Benchmark_Key, ', ')

I want to calculate the AVG of these funds removing the Fund_Name column having basically one line showing the Average Called %.

I have tried :

avg(aggr( SUM({<Data_Type={"Trans"},Fund_Name,Benchmark_Key={"$(vBenchmarkField)"}, Data_Measure={"Dist"}, Date_ID={"<=$(vReportDate)"}>} $(vMeasure))

/

-SUM({<Data_Type={"Trans"},Fund_Name,Benchmark_Key={"$(vBenchmarkField)"}, Data_Measure={"Cost"}, Date_ID={"<=$(vReportDate)"}>} $(vMeasure)),Benchmark_Key,Fund_Name))

but as soon I put the Fund_Name in the aggr formula is basically overriding my "Fund_Name=" in set analysis and showing me only the value for that fund  I have selected.

Any thoughts ?

[sunny_talwar · Accepted solution]

Try this

Avg({<Fund_Name>} Aggr(
Sum({<Data_Type = {"Trans"}, Fund_Name, Benchmark_Key = {"$(vBenchmarkField)"}, Data_Measure = {"Dist"}, Date_ID = {"<=$(vReportDate)"}>} $(vMeasure))
/
-Sum({<Data_Type = {"Trans"}, Fund_Name, Benchmark_Key ={"$(vBenchmarkField)"}, Data_Measure = {"Cost"}, Date_ID = {"<=$(vReportDate)"}>} $(vMeasure))
, Benchmark_Key, Fund_Name))

[mikecherry]

Thank you so much ! As usual you are the best.. 😄