DAX Formula - Sum Question

[Anonymous]

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

[Ashish_Mathur]

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.

[lbendlin]

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

[Anonymous]

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

[lbendlin · 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] )

see attached

[Anonymous]

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