Total measure does not consider blank rows

[calerof]

Hi,

A little help would be very much appreciated.

I have a simple formula that in MS Excel looks like this and I can't replicate it in DAX:

Shift	Hours/Shift	MinutesWithFailures (MinNoTRSDer)	FailureTime% (NoTRSDerTotal)
1	8	37	7.7083%
2	7		0.0000%
3	9		0.0000%
Total	24	37	2.5694%

FailureTime% = MinutesWithFailures / ( Hours/Shift * 60 )

My DAX formula is this:

NoTRSDerTotal = 
VAR __table = SUMMARIZE(Table1,[Shift],"__value",[NoTRSDer])
RETURN
IF(HASONEFILTER(Table1[Shift]),[NoTRSDer],AVERAGEX(__table,[__value]))

Where "NoTRSDer" is:

NoTRSDer = 
CALCULATE(
    SUMX(Table1, DIVIDE( [Sum MinNoTRSDer], Table2[Sum WorkingHours] * 60 )
))

And Table2 is just:

Shift	WorkingHours
1	8
2	7
3	9

My result is this:

The result by shift is correct, in this case:

37 / (8*60) = 0.0770833

And for the total I would expect:

37 / (24*60) = 0.0256944

Here is my pbix file.

Thanks a lot for your help!

Fernando

[HotChilli · Accepted solution]

A few things:

Are we talking about the visual halfway down the right side of the 2nd(!) page called 'Duplicate of Page 1'?

I don't like the CALCULATE round the SUMX.

The 'Turno' field in the matrix is the field from the Fact table.  If you want the filtering to work properly, it should be the Turno from the dimension table  'HorasxTurno'.

The measure (SUMX) uses 2 measures.  I would say that this is unusual.  You've really got to be on top of how these measures are evaluated in the current context to use them like this.  It is hard enough to know what happens to one measure when it is nested, without using 2.

You can assign the measure to a variable so that it doesn't change on each iteration of the SUMX.  Again, when using this technique, you have to know the context very well. 

I was able to get the answer required by replacing the Turno in the matrix and altering the measure as follows:

NoTRSDer = VAR _sum = [Sum Horas Laborables]
RETURN
 SUMX(CapturaProduccion, DIVIDE( [Sum MinNoTRSDer], _sum * 60 ))

You will need to test it it and do some impact analysis if you use the measure in other places.  Good luck.

[calerof]

Hi @HotChilli ,

Thank you for taking the time to review my model and provide the solution. I'm dealing with a different kind of animal here that I'm used to. And you are correct, I need to use the value coming from the mesaure in another places without the context with which it was originally calculated. It's silly how in Excel is so easy to do. I'm thinking on building an internal table to store the values and be able to manipulate them later for averages calculation for instance. 

Best regards,

F