Average is not working.

[#1]

I have the below expression..but it is not working...how can I make thi s work

avg(((Sum({$<[Lease Type]-={'Our Subtenant'}>}[Rentable Area (sq ft)])) +

(Sum({$<[Lease Type]={'Our Subtenant'}>}[Rentable Area (sq ft)])* -1)))

Thanks much.

[sunny_talwar]

Pavana the syntax isn't right. Avg(Sum(...)) will never work. You need to add Aggr() between them... with some dimensions over which you are doing the inner aggregation which is ultimately used for Avg(). But without more details, it would be difficult to say more. Can you may be provide a sample with expected output?

Avg(Aggr(RangeAvg(Sum({$<[Lease Type]-={'Our Subtenant'}>}[Rentable Area (sq ft)]), -Sum({$<[Lease Type] ={'Our Subtenant'}>}[Rentable Area (sq ft)])), SomeDimension/sHere))

Tried to simplify your expression a little bit. You just need to add Dimension or Dimensions on which you want to do the inner aggregation.

[#3]

This is what my data looks like and I am converting Rentable area to negative value where Lease type is subtenant and then I need the avg for the Rentable area.

t

[sunny_talwar · Accepted solution]

May be this:

Avg(Aggr(RangeAvg(Sum({$<[Lease Type]-={'Our Subtenant'}>}[Rentable Area (sq ft)]), -Sum({$<[Lease Type] ={'Our Subtenant'}>}[Rentable Area (sq ft)])), building))

[swuehl]

You could also create a flag in your script, like

LOAD

     building,

     [Rentable Area (sq ft)],

     [Lease Type],

     If([Lease Type] = 'Our Subtenant',-1,1) as factor

FROM ...;

Then your aggregation would boil down to

=Sum( [Rentable Area (sq ft)] *factor)

And for the average I would use

=Sum( [Rentable Area (sq ft)] *factor) / Count(DISTINCT building)