Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello Community.
I have tried finding solutions in other posts but have not been able to understand how to implement those actions to my code.
I have a issue where I get the wrong subtotals/totals when using my measure in a data matrix.
Right matrix compares current year to previous year, and does not show correct values for WC. So i tried changing the code as explained for left matrix.
Left matrix total is correct calculated for 31200 (61569,68) and 31201 (93504,04), but YearMonth total is incorrect. i.e. 202003 (105003,75) should result in 50087,50.
LEFT matrix code.
MQ EFFECT TO SAMEPERIODLASTYEAR =
SUMX
(VALUES(ManfData[WorkCenter]),
(
(
(
DIVIDE(
sum(ManfData[ManufacturedQuantity]),
CALCULATE(AVERAGE(ManfData[ManufacturedQuantity]),SAMEPERIODLASTYEAR('Calendar'[Date]))
)
-
COUNT(ManfData[MONumber])
)
*
AVERAGE(ManfData[SetupTime])
)
*
AVERAGE(ManfData[SumOfRates])
)
)
RIGHT matrix code.
MQ EFFECT TO PY =
SUMX(VALUES(ManfData[WorkCenter]),
(
(
(
DIVIDE(
CALCULATE(sum(ManfData[ManufacturedQuantity]),YEAR('Calendar'[Date])=(YEAR(TODAY()))),
CALCULATE(AVERAGE(ManfData[ManufacturedQuantity]),YEAR('Calendar'[Date])=(YEAR(TODAY())-1))
)
-
CALCULATE(COUNT(ManfData[MONumber]),YEAR('Calendar'[Date])=(YEAR(TODAY())))
)
*
CALCULATE(AVERAGE(ManfData[SetupTime]),YEAR('Calendar'[Date])=(YEAR(TODAY())))
)
*
CALCULATE(AVERAGE(ManfData[SumOfRates]),YEAR('Calendar'[Date])=(YEAR(TODAY())))
)
)
Thank you for any assistance!
Solved! Go to Solution.
I reworked this by changing the Year filters in calculations like below. It gave the desired result.
MOQ SAVINGS PY =
IF([MOQ SAVINGS PERIOD]=0,0,
sumx(VALUES(ManfData[WorkCenter]),
(
//
(
DIVIDE(
CALCULATE(sum(ManfData[ManufacturedQuantity]),DATESBETWEEN('Calendar'[Date],MIN('Calendar'[Date]),max('Calendar'[Date]))),
CALCULATE(AVERAGE(ManfData[ManufacturedQuantity]),PREVIOUSYEAR('Calendar'[Date])))
-
CALCULATE(COUNT(ManfData[MONumber]),DATESBETWEEN('Calendar'[Date],MIN('Calendar'[Date]),max('Calendar'[Date])))
)
*
CALCULATE(AVERAGE(ManfData[SetupTime]),DATESBETWEEN('Calendar'[Date],MIN('Calendar'[Date]),max('Calendar'[Date])))
)
*
CALCULATE(AVERAGE(ManfData[SumOfRates]),DATESBETWEEN('Calendar'[Date],MIN('Calendar'[Date]),max('Calendar'[Date])))
))
However when I laborated with this further and involved calculations with different number of personnel to be multiplied with the hours depending on the Item, I loose the sum on date again.
Code for the picture below.
= if(TOTALYTD(sum(ManfData[ManufacturedQuantity])>0,'Calendar'[Date]),
sumx(values(EOQ[Item]),sumx(VALUES('Calendar'[Year]),
(//Årets teoretiska ställkostad med fg års snitt antal/order.
( //Maskin ställ kost
(DIVIDE
(//Årets teoretiskt antal ställ med fg års snitt antal/order
TOTALYTD(sum(ManfData[ManufacturedQuantity]),'Calendar'[Date]),
CALCULATE(AVERAGE(ManfData[ManufacturedQuantity]),PREVIOUSYEAR('Calendar'[Date]))
)
)*AVERAGE(ManfData[FINAL.SETUPTIME.MACHINE])*max(ManfData[MachineRate])//Maskinkostnad
)
+
( //Personal ställ kost
(DIVIDE
(//Årets teoretiskt antal ställ med fg års snitt antal/order
TOTALYTD(sum(ManfData[ManufacturedQuantity]),'Calendar'[Date]),
CALCULATE(AVERAGE(ManfData[ManufacturedQuantity]),PREVIOUSYEAR('Calendar'[Date]))
)
)*AVERAGE(ManfData[FINAL.SETUPTIME.PERSON])*max(ManfData[LabourRate])//Personalkostnad
)
)
-
(
((TOTALYTD(COUNT(ManfData[MONumber]),'Calendar'[Date]))*AVERAGE(ManfData[FINAL.SETUPTIME.MACHINE])*max(ManfData[MachineRate]))//Årets ställkostnad maskin
+
((TOTALYTD(COUNT(ManfData[MONumber]),'Calendar'[Date]))*AVERAGE(ManfData[FINAL.SETUPTIME.PERSON])*max(ManfData[LabourRate]))//Årets ställkostnad personal
)
)))
I will try attaching a file.
Hi @lukasjar ,
This has to do with the context of your measure and with the type of calculation, in this case since you hare making the SUMX at a WorkCenter level the when you pickup the month values (row level) that is the calculation that continues to be made.
This is even more particulary since you are making the division of several calcutions based on different values.
Can you share a sample file?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsI reworked this by changing the Year filters in calculations like below. It gave the desired result.
MOQ SAVINGS PY =
IF([MOQ SAVINGS PERIOD]=0,0,
sumx(VALUES(ManfData[WorkCenter]),
(
//
(
DIVIDE(
CALCULATE(sum(ManfData[ManufacturedQuantity]),DATESBETWEEN('Calendar'[Date],MIN('Calendar'[Date]),max('Calendar'[Date]))),
CALCULATE(AVERAGE(ManfData[ManufacturedQuantity]),PREVIOUSYEAR('Calendar'[Date])))
-
CALCULATE(COUNT(ManfData[MONumber]),DATESBETWEEN('Calendar'[Date],MIN('Calendar'[Date]),max('Calendar'[Date])))
)
*
CALCULATE(AVERAGE(ManfData[SetupTime]),DATESBETWEEN('Calendar'[Date],MIN('Calendar'[Date]),max('Calendar'[Date])))
)
*
CALCULATE(AVERAGE(ManfData[SumOfRates]),DATESBETWEEN('Calendar'[Date],MIN('Calendar'[Date]),max('Calendar'[Date])))
))
However when I laborated with this further and involved calculations with different number of personnel to be multiplied with the hours depending on the Item, I loose the sum on date again.
Code for the picture below.
= if(TOTALYTD(sum(ManfData[ManufacturedQuantity])>0,'Calendar'[Date]),
sumx(values(EOQ[Item]),sumx(VALUES('Calendar'[Year]),
(//Årets teoretiska ställkostad med fg års snitt antal/order.
( //Maskin ställ kost
(DIVIDE
(//Årets teoretiskt antal ställ med fg års snitt antal/order
TOTALYTD(sum(ManfData[ManufacturedQuantity]),'Calendar'[Date]),
CALCULATE(AVERAGE(ManfData[ManufacturedQuantity]),PREVIOUSYEAR('Calendar'[Date]))
)
)*AVERAGE(ManfData[FINAL.SETUPTIME.MACHINE])*max(ManfData[MachineRate])//Maskinkostnad
)
+
( //Personal ställ kost
(DIVIDE
(//Årets teoretiskt antal ställ med fg års snitt antal/order
TOTALYTD(sum(ManfData[ManufacturedQuantity]),'Calendar'[Date]),
CALCULATE(AVERAGE(ManfData[ManufacturedQuantity]),PREVIOUSYEAR('Calendar'[Date]))
)
)*AVERAGE(ManfData[FINAL.SETUPTIME.PERSON])*max(ManfData[LabourRate])//Personalkostnad
)
)
-
(
((TOTALYTD(COUNT(ManfData[MONumber]),'Calendar'[Date]))*AVERAGE(ManfData[FINAL.SETUPTIME.MACHINE])*max(ManfData[MachineRate]))//Årets ställkostnad maskin
+
((TOTALYTD(COUNT(ManfData[MONumber]),'Calendar'[Date]))*AVERAGE(ManfData[FINAL.SETUPTIME.PERSON])*max(ManfData[LabourRate]))//Årets ställkostnad personal
)
)))
I will try attaching a file.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |