aggr(RangeSum(Above( based on 2 dimensions in a pivot table.

[stevietm]

Hi All,

I hope someone can help me. I currently want to do an aggr(RangeSum(Above( based on 2 dimensions in a pivot table.

The table have to be able to expand the period dimensions to show the breakdown per company. 
Find attached an excel example.

Thanks in advance. 

Note: My measures are rows and my dimensions are columns.

[sunny_talwar]

May be this

Aggr(
  RangeSum(Above(
    Sum({<FinYear = {'2019','2018','2017','2016'}, ManagementMap4 = {'AUDIT FEE'}, CompanyName>} -Amount)
  , 0, RowNo()))
, CompanyName, Period)

[stevietm]

Hi,

Thanks, the numbers seems to be correct now but the non expanded period gives 0's back. Only when expanding it shows the amounts.

Thanks

[sunny_talwar · Accepted solution]

How about this

Sum(Aggr(
  RangeSum(Above(
    Sum({<FinYear = {'2019','2018','2017','2016'}, ManagementMap4 = {'AUDIT FEE'}, CompanyName>} -Amount)
  , 0, RowNo()))
, CompanyName, Period))

[stevietm]

Thanks bud its work.