Sum if with distinct

[Patman]

Please advise, just getting used to the syntax 😞

I have an expression which works, but I need to sum not count the data in Column Q4 (Will be values eg 1,2,3...)

Count({<Department = {"Dept1"}, Q4= {">0"}>}Distinct "Doc ID")

Sum Q4 when Department = Dept1 but only doc ID is unique, there are times when the same doc ID is in multiple rows.

[sunny_talwar]

May be this

Sum(Aggr(
  Only({<Department = {"Dept1"}, Q4= {">0"}>} Q4)
, [Doc ID]))

[Patman]

Cheers for that, but it seems to return a larger number than expected, perhaps it is counting without distinct Doc ID?

[sunny_talwar]

It should be just picking a single Q4 value per Doc ID... for instance...

Doc ID                Q4
123                       10
123                       10
345                       20
345                       20
345                       20

Should give you 30

[Patman]

Many thanks :), I cleaned up my source data and that's working perfect now 🙂 Cheers