Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi all,
It seems to me, it would easier to do, but I'm having a hard time to sum a value in my table.
I have the following relationship between my tables:
I calculated the total production, and it appers ok when I check by using a dashboard filter.
Now I want to calculate the "total family production", and show the same value for each material which belongs for this "family".
I tried to use
Solved! Go to Solution.
Thank you!
Please 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.
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.
I would combine the Unit Price and BOM tables. Any reason not to?
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
109 | |
108 | |
93 | |
61 |
User | Count |
---|---|
171 | |
139 | |
133 | |
102 | |
86 |