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.
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.
|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|
|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|
|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 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;
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|
On the example above, I could find the "Total Production" of Family Model A from its related Report (Report July), by using the formula:
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.
Click here to read more about the November 2022 updates!
Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.
This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.
Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.