Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
BXL
Frequent Visitor

DAX to add field value based on other

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.

 

 

Capture.JPG

 

 

1 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. Smiley Happy

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

c1.PNG

 

Regards

View solution in original post

4 REPLIES 4

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. 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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.

 

Capture.JPG 

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. Smiley Happy

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

c1.PNG

 

Regards

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")



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.