Forum Discussion
SUM using ALLSELECTED from other table
I would combine the Unit Price and BOM tables. Any reason not to?
Super UserPlease indicate the expected outcome. Also, read about ALLEXCEPT() - that might be more appropriate.
My expected outcome is each item shows the total value of it family (repeated), as below. However, I got this value by using some filter on the table, and I would like to have this value in order to multiply for another calculation.
- lbendlin
While 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?