Forum Discussion
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!!!
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
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/1447523Hello 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
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.
