cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Calveira
Frequent Visitor

SUM using ALLSELECTED from other table

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:

Calveira_0-1659032957381.png

 

I calculated the total production, and it appers ok when I check by using a dashboard filter.

 

Calveira_1-1659033182425.png

 

 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?
1 ACCEPTED SOLUTION

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

 

lbendlin_0-1659721463214.png

lbendlin_1-1659722769206.png

 

View solution in original post

6 REPLIES 6
Calveira
Frequent Visitor

Thank you!

lbendlin
Super User
Super User

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.

Calveira_0-1659359519587.png

 

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     
ReportModelRaw MaterialUnitQuantityDate
Report JuneModel A123Raw Material 1KG0,25ago/22
Report JuneModel A123Raw Material 2KG0,25ago/22
Report JuneModel A123Raw Material 3KG0,5ago/22
Report JuneModel B123Raw Material 1KG0,5ago/22
Report JuneModel B123Raw Material 2KG0,3ago/22
Report JuneModel B123Raw Material 3KG0,2ago/22
Report JuneModel A123Raw Material 1KG0,25set/22
Report JuneModel A123Raw Material 2KG0,25set/22
Report JuneModel A123Raw Material 3KG0,5set/22
Report JuneModel B123Raw Material 1KG0,5set/22
Report JuneModel B123Raw Material 2KG0,3set/22
Report JuneModel B123Raw Material 3KG0,2set/22
Report JuneModel A123Raw Material 1KG0,25out/22
Report JuneModel A123Raw Material 2KG0,25out/22
Report JuneModel A123Raw Material 3KG0,5out/22
Report JuneModel B123Raw Material 1KG0,5out/22
Report JuneModel B123Raw Material 2KG0,3out/22
Report JuneModel B123Raw Material 3KG0,2out/22
Report JulyModel A123Raw Material 1KG0,25ago/22
Report JulyModel A123Raw Material 2KG0,25ago/22
Report JulyModel A123Raw Material 3KG0,5ago/22
Report JulyModel B123Raw Material 1KG0,5ago/22
Report JulyModel B123Raw Material 2KG0,3ago/22
Report JulyModel B123Raw Material 3KG0,2ago/22
Report JulyModel A123Raw Material 1KG0,25set/22
Report JulyModel A123Raw Material 2KG0,25set/22
Report JulyModel A123Raw Material 3KG0,5set/22
Report JulyModel B123Raw Material 1KG0,5set/22
Report JulyModel B123Raw Material 2KG0,3set/22
Report JulyModel B123Raw Material 3KG0,2set/22
Report JulyModel A123Raw Material 1KG0,25out/22
Report JulyModel A123Raw Material 2KG0,25out/22
Report JulyModel A123Raw Material 3KG0,5out/22
Report JulyModel B123Raw Material 1KG0,5out/22
Report JulyModel B123Raw Material 2KG0,3out/22
Report JulyModel B123Raw Material 3KG0,2out/22

 

UnitPrice_Table   
ReportRaw MaterialUnitPriceDate
Report JuneRaw Material 1KG1,50ago/22
Report JuneRaw Material 2KG5,70ago/22
Report JuneRaw Material 3KG3,00ago/22
Report JuneRaw Material 1KG1,50set/22
Report JuneRaw Material 2KG5,70set/22
Report JuneRaw Material 3KG3,05set/22
Report JuneRaw Material 1KG1,80out/22
Report JuneRaw Material 2KG5,75out/22
Report JuneRaw Material 3KG3,15out/22
Report JulyRaw Material 1KG1,50ago/22
Report JulyRaw Material 2KG5,70ago/22
Report JulyRaw Material 3KG3,05ago/22
Report JulyRaw Material 1KG1,65set/22
Report JulyRaw Material 2KG5,70set/22
Report JulyRaw Material 3KG3,05set/22
Report JulyRaw Material 1KG1,80out/22
Report JulyRaw Material 2KG5,75out/22
Report JulyRaw Material 3KG3,15out/22

 

Production_Table   
ReportModelUnitQty_ProdDate
Report JuneModel A123KG40ago/22
Report JuneModel A456KG60ago/22
Report JuneModel B123KG110ago/22
Report JuneModel B456KG90ago/22
Report JuneModel A123KG45set/22
Report JuneModel A456KG40set/22
Report JuneModel B123KG100set/22
Report JuneModel B456KG120set/22
Report JuneModel A123KG55out/22
Report JuneModel A456KG60out/22
Report JuneModel B123KG80out/22
Report JuneModel B456KG120out/22
Report JulyModel A123KG48ago/22
Report JulyModel A456KG69ago/22
Report JulyModel B123KG114ago/22
Report JulyModel B456KG99ago/22
Report JulyModel A123KG54set/22
Report JulyModel A456KG47set/22
Report JulyModel B123KG110set/22
Report JulyModel B456KG125set/22
Report JulyModel A123KG61out/22
Report JulyModel A456KG73out/22
Report JulyModel B123KG88out/22
Report JulyModel B456KG125out/22

 

Model_Table
ModelFamily
Model A123Model A
Model A456Model A
Model B123Model B
Model B456Model 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

ReportDateModelRaw MaterialUnitQuantityUnit CostTotal Unit CostProd Family JuneProd Family JulyTotal Cost Report JuneTotal Cost Report July
Report Julyago/22Model A123Raw Material 1KG0,251,500,38100,00117,0037,5043,88
Report Julyago/22Model A123Raw Material 2KG0,255,71,43100,00117,00142,50166,73
Report Julyago/22Model A123Raw Material 3KG0,53,051,53100,00117,00152,50178,43
       3,33  332,50389,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?

 

lbendlin_0-1659721463214.png

lbendlin_1-1659722769206.png

 

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.