SUM using ALLSELECTED from other table

[Anonymous]

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 

CALCULATE(SUM(Production[Quantity]),ALL(Material_FG[Family (1)]))

and 

CALCULATE(SUM(Production[Quantity]),ALLSELECTED(Material_FG[Family (1)]))

but it doesn't work as expected. 

Is there a problem that I'm not seeing?

[Anonymous]

Thank you!

[lbendlin]

Please indicate the expected outcome.  Also, read about ALLEXCEPT() - that might be more appropriate.

[Anonymous]

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.

[Anonymous]

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 · Accepted solution]

I would combine the Unit Price and BOM tables.   Any reason not to?