Aggregation along a selected dimension

[#1]

Stupid question, I am sure...

I'd like to understand how I can sum the Bill amount (see table below) by selecting the dimension along which I'd like to aggregate.

In plain english, this would be: "sum the Billing amount by distinct Order ID (to avoid summing up every rows"

Expected answer = 100 + 200 + 400 = 700

Who can help me get the right syntax?

Order ID	Item name	Item QTY	Item price	Bill amount
B1	Item A	1	20	100
B1	Item B	2	40	100
B1	Item C	2	40	100
B2	Item A	2	40	200
B2	Item D	1	160	200
B3	Item A	1	20	400
B3	Item B	2	40	400
B3	Item E	4	340	400

[Nicole-Smith]

sum(aggr(only([Bill amount]),[Order ID]))

[#3]

Hi,

you can use sum(DISTINCT Bill Amount)

[#4]

Hey Jean,

I this the final view of the table?  Can you post an example of how the final table will look like. What all dimensions and expressions if different from this. Or do you want this 700 in a text box?

You can try   sum(aggr(sum(distinct Bill amount),OrderID)) 

This will work in Text Box perfectly but in table it will cause nulls for everything bur first Order ID but total will be correct.

It depends on where you want the result.

[Nicole-Smith]

Torsten Rehder's solution will only work if two Order ID's never have the same Bill Amount.