Forum Discussion
DAX Formula - Sum Question
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
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 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 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
This worked PERFECTLY for us! Thank you so much!!!