cancel
Showing results 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

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!!!

1 ACCEPTED SOLUTION
Super User

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] )``````

see attached

5 REPLIES 5
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
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.

Frequent Visitor

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 ITEM MATERIAL TRANSACTION LOCATION LOCATION ON HAND EXTENDED COST TRANS_TYPE UNIT COST WHSE 9150006204 ET-01B 74 68.82 Issue/WIP Change 0.93 1 9300241230 ET-15C 3 109.89 Issue/WIP Change 36.63 1 100177 ET-35A 0 0 Issue/WIP Change 38.81 1 100-C30D00 ET-23B 3 267.09 Ship 89.03 1 101 ET-13A 259 20.72 Issue/WIP Change 0.08 1 1139-032-327 ET-33C 7 3288.6 Issue/WIP Change 469.8 1 1161 ET-15A 66 39.3954 Issue/WIP Change 0.5969 1 1-178288-3 ET-34A 93 35.433 Issue/WIP Change 0.381 1 1-178288-3 ET-34A 93 35.433 Ship 0.381 1 11855A18 ET-02B 7 171.08 Issue/WIP Change 24.44 1 11855A18 ET-02B 7 171.08 Ship 24.44 1 1-206062-6 ET-34B 12 37.44 Issue/WIP Change 3.12 1 120861 ET-35A 1 175.12 Issue/WIP Change 175.12 1 TOTAL \$           4,420.10 -35.433 -171.08 Removed Extra Transaction Type from total \$           4,213.59

Super User

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] )``````

see attached

Frequent Visitor

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

Announcements

#### 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.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

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

Top Solution Authors
Top Kudoed Authors