Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lukasjar
Resolver I
Resolver I

SUMX in Data matrix incorrect subtotals

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.

 

matrix.PNG

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!

 

1 ACCEPTED 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.

 

Item total correct, date total incorrect.Item total correct, date total incorrect.

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.

View solution in original post

3 REPLIES 3
lukasjar
Resolver I
Resolver I

@MFelix file for PBIX can be found on below link.

 

PBIX File 

MFelix
Super User
Super User

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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.

 

Item total correct, date total incorrect.Item total correct, date total incorrect.

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.