Can't fix "Wrong" Total in Matrix

[Micha3lS]

Hello all,

I currently have the problem, that I can't get the total to work. I tried to use SUMX to aggregrate and Prodcutgroup level, but still I'm not getting the correct Reulst.

The formula looks like this:

Target = 
Var _AvgCyclTime = Calculate(                    
                    Divide(SUM(PlanData[Fertigungszeit Verteilzeit]),SUM(PlanData[Vorgangsmenge])),      
                    FILTER(PlanData, PlanData[Produktgruppe] <> "Collabs")
)

RETURN
CALCULATE(
    SUMX(
        VALUES(ProductionLines[Produktgruppe]),
        Divide('scheduled production shifts'[Capacity], _AvgCyclTime)* MIN(PlanData[TargetOEE])
    ),
    KEEPFILTERS(PlanData[Produktgruppe] <> "Collabs")
)

Scheduled production shifts'[capacity'] is another is another measure:

Capacity = Sum('scheduled production shifts'[Scheduled Time sec])

The Visual looks like this:

In the rows it works exactly like intendet, but I can't get the result like shown here: 
https://www.sqlbi.com/articles/obtaining-accurate-totals-in-dax/

I really can't get my head around it and would appreciate an help.
Thank you

Michael

[Micha3lS · Accepted solution]

I finally found the Solution.

My Calculate was at the wrong spot. Here is the working code:

Target = 
SUMX(
    VALUES(ProductionLines[Produktgruppe]),
    CALCULATE(
        Divide('scheduled production shifts'[Capacity], Divide(SUM(PlanData[Fertigungszeit Verteilzeit]),SUM(PlanData[Vorgangsmenge]))) * MIN(PlanData[TargetOEE]),
        KEEPFILTERS(ProductionLines[Produktgruppe] <> "Collabs")
    )
)

However putting parts of the Divide into a variable with the same logic is not working. Can you explain why? Or do I only add the modifiers like Filters etc. in the Return and in the variable I only write the formula itself without the other stuff??

[AlexisOlson]

One potential issue here is that your MIN is not operating in the row context that you're iterating over. That is, it's taking the min over all the Produktgruppe values in the local filter context rather than performing a context transition and only considering the value in the row context.

If this is the only problem, you can correct it by wrapping it with CALCULATE to force a context transition.

Target =
VAR _AvgCyclTime =
    CALCULATE (
        DIVIDE (
            SUM ( PlanData[Fertigungszeit Verteilzeit] ),
            SUM ( PlanData[Vorgangsmenge] )
        ),
        FILTER ( PlanData, PlanData[Produktgruppe] <> "Collabs" )
    )
RETURN
    CALCULATE (
        SUMX (
            VALUES ( ProductionLines[Produktgruppe] ),
            DIVIDE ( 'scheduled production shifts'[Capacity], _AvgCyclTime )
                * CALCULATE ( MIN ( PlanData[TargetOEE] ) )
        ),
        KEEPFILTERS ( PlanData[Produktgruppe] <> "Collabs" )
    )

[Micha3lS]

Thanks for your Input, unfotrunately that didn't change the Total Result.

[tamerj1]

Hi @Micha3lS 

can you please share a screenshot of your data model? 
in the visual, what are slicing by?

[Micha3lS]

Hi @tamerj1,

Im slicing by the location and a maschinegroup in addtion to the date and year. 

The location and machinegroup are stored in the same table as the Produktgruppe in the Table Productionlines.

Here is a Screenshot of the relevant tables:

[tamerj1]

Hi @Micha3lS 

you need to wrap DIVIDE inside SUMX with CALCULATE

[Micha3lS]

Hi @tamerj1 ,

That didn't work either, unfrotunately. Nothing changed.