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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
cswinimer
Frequent Visitor

DAX Formula - Sum Question

Hello,

 

I am trying to sum up the total extended cost of our items.  I am unsure how to filter some of the results, however, and need help.

 

In the screenshot below, you can see that I've filtered to one item.  I am performing the following calculation to determine extended cost. 

 

LOCATION ON HAND * UNIT COST = EXTENDED COST

In the example below it would be (93.00 * 0.38 = 35.43)  This is approximate due to rounding.

 

Issue:  As seen in the example below, an item can appear twice because it has a different transaction type.  One is "Issue/WIP Change" and the other is "Ship".  Adding these two together would not be correct for what we are trying to accomplish.  If this combination exists, like in the example below, we only want to take the TRANS_Type = Issue/WIP Change (add 35.43 to the sum in the example below).   

 

We do want to add these when TRANS_TYPE = Issue/WIP Change     or    TRANS_TYPE = Ship.

 

Our goal is to avoid the sum of extended cost by taking one TRANS_TYPE when "Issue/WIP Change" AND "Ship" appear together.

 

Is there a DAX formula that can help us accomplish this?  Thank you for your help!!!

cswinimer_0-1654263660347.png

 

1 ACCEPTED SOLUTION

Thank you for providing the sample data. That helps a lot with proposing a potential solution.

 

Include =
VAR a =
    SUMMARIZE (
        'Table',
        'Table'[MATERIAL TRANSACTION ITEM],
        'Table'[MATERIAL TRANSACTION LOCATION],
        "Issue", CALCULATE (SUM ( 'Table'[EXTENDED COST] ),'Table'[TRANS_TYPE] = "Issue/WIP Change"),
        "Ship", CALCULATE ( SUM ( 'Table'[EXTENDED COST] ), 'Table'[TRANS_TYPE] = "Ship" )
    )
VAR b =
    ADDCOLUMNS ( a, "sm", IF ( [Issue] <> 0, [Issue], [Ship] ) )
RETURN
    SUMX ( b, [sm] )

 

lbendlin_0-1654549634567.png

see attached

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

Hi,

What result are you expecting in each row of the visual.  Should it be =35.43/2?  Please clarify.  Share the download link of your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
lbendlin
Super User
Super User

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. I cannot use screenshots of your source data.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Hello Ibendlin,

 

Thanks so much for reaching out and offering your help.  My team is new to Power BI and this community so I apologize for not providing all the information upfront.  I have attached an Excel doc with some sample data.  

 

The table below shows what we are expecting.  When we come across a set of data such as this example, where there is a duplication in the "MATERIAL TRANSACTION ITEM" and "MATERIAL TRANSACTION LOCATION" columns we would like the formula to not sum both values in the "EXTENDED COST" column when "TRANS_TYPE" type is equal to "Issue/WIP Change" and "Ship", we can ignore the value that is associated with "Ship" as the "TRANS_TYPE" (Red Font in the table below).  

 

**Please note, that if "Ship" or "Issue/WIP Change" appears on its own, then we do want to include that into the total of "EXTENDED COST".   

 

MATERIAL TRANSACTION ITEMMATERIAL TRANSACTION LOCATIONLOCATION ON HANDEXTENDED COSTTRANS_TYPEUNIT COSTWHSE
9150006204ET-01B7468.82Issue/WIP Change0.931
9300241230ET-15C3109.89Issue/WIP Change36.631
100177ET-35A00Issue/WIP Change38.811
100-C30D00ET-23B3267.09Ship89.031
101ET-13A25920.72Issue/WIP Change0.081
1139-032-327ET-33C73288.6Issue/WIP Change469.81
1161ET-15A6639.3954Issue/WIP Change0.59691
1-178288-3ET-34A9335.433Issue/WIP Change0.3811
1-178288-3ET-34A9335.433Ship0.3811
11855A18ET-02B7171.08Issue/WIP Change24.441
11855A18ET-02B7171.08Ship24.441
1-206062-6ET-34B1237.44Issue/WIP Change3.121
120861ET-35A1175.12Issue/WIP Change175.121
  TOTAL $           4,420.10   
   -35.433   
   -171.08   
  Removed Extra Transaction Type from total $           4,213.59   

 

 

Thank you for providing the sample data. That helps a lot with proposing a potential solution.

 

Include =
VAR a =
    SUMMARIZE (
        'Table',
        'Table'[MATERIAL TRANSACTION ITEM],
        'Table'[MATERIAL TRANSACTION LOCATION],
        "Issue", CALCULATE (SUM ( 'Table'[EXTENDED COST] ),'Table'[TRANS_TYPE] = "Issue/WIP Change"),
        "Ship", CALCULATE ( SUM ( 'Table'[EXTENDED COST] ), 'Table'[TRANS_TYPE] = "Ship" )
    )
VAR b =
    ADDCOLUMNS ( a, "sm", IF ( [Issue] <> 0, [Issue], [Ship] ) )
RETURN
    SUMX ( b, [sm] )

 

lbendlin_0-1654549634567.png

see attached

This worked PERFECTLY for us!   Thank you so much!!!

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.