Would really appreciate some help as I am stuck. I would like to sum a column named values for all distinct orders.
Basically the values for each order reference will be the same so I just want to sum the value for each unique order reference.
Would anyone be able to help me please? Also sum (distinct OrderRefernece) won't work as some order references will have the same value as another order reference.
Thanks Sunny, this has been really helpful. Can I just confirm my logic as to what's happening here?
Are we aggregating order reference and summing the value?
Thanks again for your help, really appreciate it.
When you close the thread Sunny's answer should be marked as correct but in the meantime if I may I'd like to explain how the Aggr function is working here.
Aggr(Value, OrderReference) creates what may be described as an in-memory straight table with expression Only(Value) and dimension OrderReference. If you could see the table it would look like this:
The Aggr function returns an array of values for Only(Value) - the values for each distinct OrderReference. You want the sum of these so we wrap the sum() function around the Aggr expression. It's a bit like setting the Total Mode in a straight table to the Sum of Rows:
Just as you can set the Total Mode in a straight table to Avg, Min, Max, etc you can use the aggregation function of your choice to wrap around the Aggr expression.