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