Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have this measure that makes averages of value for the rows where Source is AAA, BBB, CCC, and now I need to make the sum of all these 3 averages on a new row in the same pivot table.
if(match(Source,'AAA','BBB','CCC'),
Num(Avg({<Source={'AAA','BBB','CCC'}
,
Help_Column={'DDD'}>}[Value]), '#,##0', '.', ','))
Any idea how can I do this?
the IF() seems redundant as the set analysis already filters for those SOURCE values
=Num(
SUM ( AGGR (
Avg({<Source={'AAA','BBB','CCC'},Help_Column={'DDD'}>}[Value]) , DIMENSION1 , DIMENSION2, ...... ) )
, '#,##0', '.', ',')
DIMENSION1 , DIMENSION2 .. are the dimensions used in your chart
Use the the AGGR() Function to sum the averages at the higher level of granularity.
sum(Aggr(if(match(Source,'AAA','BBB','CCC'),Avg({<Source={'AAA','BBB','CCC'},Help_Column={'DDD'}>}[Value]), Source)))
the IF() seems redundant as the set analysis already filters for those SOURCE values
=Num(
SUM ( AGGR (
Avg({<Source={'AAA','BBB','CCC'},Help_Column={'DDD'}>}[Value]) , DIMENSION1 , DIMENSION2, ...... ) )
, '#,##0', '.', ',')
DIMENSION1 , DIMENSION2 .. are the dimensions used in your chart
Thank you both for the help!