topic Re: Can't fix "Wrong" Total in Matrix in DAX Commands and Tips

Can't fix "Wrong" Total in Matrix

Micha3lS — Fri, 25 Feb 2022 14:32:33 GMT

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

Re: Can't fix "Wrong" Total in Matrix

tamerj1 — Fri, 25 Feb 2022 16:39:09 GMT

Hi Micha3lS

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

Re: Can't fix "Wrong" Total in Matrix

AlexisOlson — Fri, 25 Feb 2022 21:43:06 GMT

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" ) )

Re: Can't fix "Wrong" Total in Matrix

Micha3lS — Sat, 26 Feb 2022 07:40:54 GMT

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

Re: Can't fix "Wrong" Total in Matrix

Micha3lS — Sat, 26 Feb 2022 07:44:45 GMT

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:

Re: Can't fix "Wrong" Total in Matrix

tamerj1 — Sat, 26 Feb 2022 12:57:51 GMT

Hi Micha3lS

you need to wrap DIVIDE inside SUMX with CALCULATE

Re: Can't fix "Wrong" Total in Matrix

Micha3lS — Sun, 27 Feb 2022 06:43:45 GMT

Hi tamerj1 ,

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

Re: Can't fix "Wrong" Total in Matrix

Micha3lS — Mon, 28 Feb 2022 09:23:45 GMT

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??