Forum Discussion
SUM using ALLSELECTED from other table
I would combine the Unit Price and BOM tables. Any reason not to?
Super UserWhile I would challenge your expected outcome (the same number repeated all over usually is a UX red flag) the suggestion to use ALLEXCEPT() still stands.
Please provide sanitized sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.
Thank you for your return lbendlin
I have 4 main tables, as example below: BOM_Table, UnitPrice_Table, Model_Table and Production_Table - also Calendar_Table.
| BOM_Table | |||||
| Report | Model | Raw Material | Unit | Quantity | Date |
| Report June | Model A123 | Raw Material 1 | KG | 0,25 | ago/22 |
| Report June | Model A123 | Raw Material 2 | KG | 0,25 | ago/22 |
| Report June | Model A123 | Raw Material 3 | KG | 0,5 | ago/22 |
| Report June | Model B123 | Raw Material 1 | KG | 0,5 | ago/22 |
| Report June | Model B123 | Raw Material 2 | KG | 0,3 | ago/22 |
| Report June | Model B123 | Raw Material 3 | KG | 0,2 | ago/22 |
| Report June | Model A123 | Raw Material 1 | KG | 0,25 | set/22 |
| Report June | Model A123 | Raw Material 2 | KG | 0,25 | set/22 |
| Report June | Model A123 | Raw Material 3 | KG | 0,5 | set/22 |
| Report June | Model B123 | Raw Material 1 | KG | 0,5 | set/22 |
| Report June | Model B123 | Raw Material 2 | KG | 0,3 | set/22 |
| Report June | Model B123 | Raw Material 3 | KG | 0,2 | set/22 |
| Report June | Model A123 | Raw Material 1 | KG | 0,25 | out/22 |
| Report June | Model A123 | Raw Material 2 | KG | 0,25 | out/22 |
| Report June | Model A123 | Raw Material 3 | KG | 0,5 | out/22 |
| Report June | Model B123 | Raw Material 1 | KG | 0,5 | out/22 |
| Report June | Model B123 | Raw Material 2 | KG | 0,3 | out/22 |
| Report June | Model B123 | Raw Material 3 | KG | 0,2 | out/22 |
| Report July | Model A123 | Raw Material 1 | KG | 0,25 | ago/22 |
| Report July | Model A123 | Raw Material 2 | KG | 0,25 | ago/22 |
| Report July | Model A123 | Raw Material 3 | KG | 0,5 | ago/22 |
| Report July | Model B123 | Raw Material 1 | KG | 0,5 | ago/22 |
| Report July | Model B123 | Raw Material 2 | KG | 0,3 | ago/22 |
| Report July | Model B123 | Raw Material 3 | KG | 0,2 | ago/22 |
| Report July | Model A123 | Raw Material 1 | KG | 0,25 | set/22 |
| Report July | Model A123 | Raw Material 2 | KG | 0,25 | set/22 |
| Report July | Model A123 | Raw Material 3 | KG | 0,5 | set/22 |
| Report July | Model B123 | Raw Material 1 | KG | 0,5 | set/22 |
| Report July | Model B123 | Raw Material 2 | KG | 0,3 | set/22 |
| Report July | Model B123 | Raw Material 3 | KG | 0,2 | set/22 |
| Report July | Model A123 | Raw Material 1 | KG | 0,25 | out/22 |
| Report July | Model A123 | Raw Material 2 | KG | 0,25 | out/22 |
| Report July | Model A123 | Raw Material 3 | KG | 0,5 | out/22 |
| Report July | Model B123 | Raw Material 1 | KG | 0,5 | out/22 |
| Report July | Model B123 | Raw Material 2 | KG | 0,3 | out/22 |
| Report July | Model B123 | Raw Material 3 | KG | 0,2 | out/22 |
| UnitPrice_Table | ||||
| Report | Raw Material | Unit | Price | Date |
| Report June | Raw Material 1 | KG | 1,50 | ago/22 |
| Report June | Raw Material 2 | KG | 5,70 | ago/22 |
| Report June | Raw Material 3 | KG | 3,00 | ago/22 |
| Report June | Raw Material 1 | KG | 1,50 | set/22 |
| Report June | Raw Material 2 | KG | 5,70 | set/22 |
| Report June | Raw Material 3 | KG | 3,05 | set/22 |
| Report June | Raw Material 1 | KG | 1,80 | out/22 |
| Report June | Raw Material 2 | KG | 5,75 | out/22 |
| Report June | Raw Material 3 | KG | 3,15 | out/22 |
| Report July | Raw Material 1 | KG | 1,50 | ago/22 |
| Report July | Raw Material 2 | KG | 5,70 | ago/22 |
| Report July | Raw Material 3 | KG | 3,05 | ago/22 |
| Report July | Raw Material 1 | KG | 1,65 | set/22 |
| Report July | Raw Material 2 | KG | 5,70 | set/22 |
| Report July | Raw Material 3 | KG | 3,05 | set/22 |
| Report July | Raw Material 1 | KG | 1,80 | out/22 |
| Report July | Raw Material 2 | KG | 5,75 | out/22 |
| Report July | Raw Material 3 | KG | 3,15 | out/22 |
| Production_Table | ||||
| Report | Model | Unit | Qty_Prod | Date |
| Report June | Model A123 | KG | 40 | ago/22 |
| Report June | Model A456 | KG | 60 | ago/22 |
| Report June | Model B123 | KG | 110 | ago/22 |
| Report June | Model B456 | KG | 90 | ago/22 |
| Report June | Model A123 | KG | 45 | set/22 |
| Report June | Model A456 | KG | 40 | set/22 |
| Report June | Model B123 | KG | 100 | set/22 |
| Report June | Model B456 | KG | 120 | set/22 |
| Report June | Model A123 | KG | 55 | out/22 |
| Report June | Model A456 | KG | 60 | out/22 |
| Report June | Model B123 | KG | 80 | out/22 |
| Report June | Model B456 | KG | 120 | out/22 |
| Report July | Model A123 | KG | 48 | ago/22 |
| Report July | Model A456 | KG | 69 | ago/22 |
| Report July | Model B123 | KG | 114 | ago/22 |
| Report July | Model B456 | KG | 99 | ago/22 |
| Report July | Model A123 | KG | 54 | set/22 |
| Report July | Model A456 | KG | 47 | set/22 |
| Report July | Model B123 | KG | 110 | set/22 |
| Report July | Model B456 | KG | 125 | set/22 |
| Report July | Model A123 | KG | 61 | out/22 |
| Report July | Model A456 | KG | 73 | out/22 |
| Report July | Model B123 | KG | 88 | out/22 |
| Report July | Model B456 | KG | 125 | out/22 |
| Model_Table | |
| Model | Family |
| Model A123 | Model A |
| Model A456 | Model A |
| Model B123 | Model B |
| Model B456 | Model B |
BOM_Table and Production_Table: Fact Table
UnitPrice_Table: It is related to BOM_Table concatenating "Year & Month & Raw Material & Report";
Model_Table: It is related to BOM_Table and Production_Table by Model column;
Expected outcome:
Multiplying each raw material x cost unit = Total Unit Cost
Then I would like to multiply the Total Unit Cost x Total Family Production from current report and last report, so I can compare the difference between them.
Example, step by step of calculation:
PS: The information that I'm going to use on my dashboard it is just Total Cost Report June and Total Cost Report July
| Report | Date | Model | Raw Material | Unit | Quantity | Unit Cost | Total Unit Cost | Prod Family June | Prod Family July | Total Cost Report June | Total Cost Report July |
| Report July | ago/22 | Model A123 | Raw Material 1 | KG | 0,25 | 1,50 | 0,38 | 100,00 | 117,00 | 37,50 | 43,88 |
| Report July | ago/22 | Model A123 | Raw Material 2 | KG | 0,25 | 5,7 | 1,43 | 100,00 | 117,00 | 142,50 | 166,73 |
| Report July | ago/22 | Model A123 | Raw Material 3 | KG | 0,5 | 3,05 | 1,53 | 100,00 | 117,00 | 152,50 | 178,43 |
| 3,33 | 332,50 | 389,03 |
On the example above, I could find the "Total Production" of Family Model A from its related Report (Report July), by using the formula:
CALCULATE(SUM(Production[Qty_Prod]),ALLEXCEPT(Production,Production[Family],Production[Report],Production[Date]))
However, I'm strugling to calculate the "Total Production" of Family Model A from last report.
There are an away to find it? So I can calculate Total Cost Report June using Total Unit Cost Report July?
I hope I could make myself clear.
- lbendlin
I would combine the Unit Price and BOM tables. Any reason not to?