Nested Aggr() function

[#1]

Hi all,

Hoping I could get some input on this - I've been stumped though I feel it should be fairly straightforward.

I have an expression currently looking like this (though evidently quite non-functional!)

sum(
Aggr(
UnitCost * aggr(sum(UnitCount) / count(DISTINCT DeviceID) ,[Department]), DeviceID)
)

If it were related to a single device (DeviceID) it would be as follows:

UnitCost * (Department Unit Count / Department Device Count)

So I want the above expression to be carried out per device; each of their unique UnitCost values multiplied by unit department's average units per device. This all has to occur within a text object to provide a single total.

The devices are added in the dashboard as a dynamic update, rather than as part of the reload. If the device count could be derived during the load script, I'd simply add a value against each department of their "Average Units Per Device" and it would be a little more straightforward.

I feel I'm missing something obvious; if anyone can provide a little input that would be great.

Thanks in advance!!

[#2]

Thanks for your response Marcus!

You're right, I was missing an aggregation!! D'oh!

Your solution wasn't yielding quite the results I expected though I'm not sure why; it looks good! Thanks for your response!

[#3]

Thanks for your reply, Sunny! I went with Gysbert's answer as I think it covered the requirement for looking at the TOTAL devices in a department, however I think your solution would have worked if I modified it to my requirements a bit.

Appreciate you answering my question - Thanks!