DAX to add field value based on other

[BXL]

Hi there,

I would like to add a new field that sums the Amount for each order# based on its Status as illustarted table below.

I know that this could be done by a Measure rather than new Field but for reporting purposes it minimises errors doing it this way.

[MattAllington]

What do you mean by field?  Do you mean calculated column?  And what do you mean when you say this is somehow less error prone?

the correct way to do this is a measure, otherwise you will have redundant data in the new column. 

[BXL]

I am new to this so its likely I'm wrong!!! But yes, I meant Calculated Column... and the reason is to make it easy for users when producing a report... they can simply select "Order Number" and "Status Amount"  without needing to select the Status in order to produce the correct "Staus Amount"

maybe I'm trying to operate it like a Pivot Table which is definately the wrong way.... Please see below extended data table and the report I wish to produce.

 

[v-ljerr-msft · Accepted solution]

Hi @BXL,

Just in case you still need the "Status Amount" as a calculate column in your scenario, the formula below is for your reference. 

Staus Amount = CALCULATE(SUM(Data[Amount]),ALLEXCEPT(Data,Data[Order Number],Data[Status]))

Regards

[MattAllington]

So it looks like you have changed your sample data. The first post it seemed to add to 25, the latest shows it adding to 30 (or I am missing something).  Are you saying you want to be able to report the total amount by order number where the status = "Approved".  If so, write the following measure.

Total = calculate(sum(dataTable[Amount]),dataTable[Status]="Approved")