Z-Score Calculation with Multiple Filters

[Anonymous]

Hello,

I am rather new to Power BI and I am currently trying to use DAX codes to generate a Measure of Z-Scores with multiple filters.

The report is using Direct Query to a SQL database.

Here is an example of part of the table:

I would like to categorize the Average Cycle Times (ACT) by Devices, and create a Measure that shows the Z-Scores of these ACTs based on averages and standard deviations for each Device.

However, I also want to ignore all the 0 entries in the ACT column.

Here is my DAX code for the Z-Scores without ignoring all the 0 entries:

ACT_Z = 
var mean = CALCULATE(AVERAGE('Test2'[ACT]),ALLEXCEPT('Test2','Test2'[Device]))
var deviation = CALCULATE(STDEV.P('Test2'[ACT]),ALLEXCEPT('Test2','Test2'[Device]))
return (SUM('Test2'[ACT])-mean)/deviation

And here is the result (I don't know if it is correct, but I think it is):

And when I add the filter for ignoring the 0s:

ACT_Z = 
var mean = CALCULATE(AVERAGE('Test2'[ACT]),ALLEXCEPT('Test2','Test2'[Device]),FILTER('Test2','Test2'[ACT]>0))
var deviation = CALCULATE(STDEV.P('Test2'[ACT]),ALLEXCEPT('Test2','Test2'[Device]),FILTER('Test2','Test2'[ACT]>0))
return (SUM('Test2'[ACT])-mean)/deviation

Here is what I got:

I can't figure out what the problem is, and if my previous DAX code was correct.

Would someone please take a look at it? I hope this is a quick fix. Thank you!

[Anonymous]

hi @Anonymous 

Would agree with @Anonymous 

Your first part of dax z-score calculation is correct, but its better to do the filter on the first part itself without trying to call twice the code.

All the best

[parry2k]

@Anonymous assume you want to exclude zero entries in all the calculations

ACT_Z = 
var mean = CALCULATE(AVERAGE('Test2'[ACT]),ALLEXCEPT('Test2','Test2'[Device]),Test2'[ACT]>0)
var deviation = CALCULATE(STDEV.P('Test2'[ACT]),ALLEXCEPT('Test2','Test2'[Device]),Test2'[ACT]>0)

return (SUM('Test2'[ACT])-mean)/deviation

[Anonymous · Accepted solution]

@Anonymous - Please try to reconfigure like this and let us know whether it works:

ACT_Z = 
var mean = CALCULATE(
	AVERAGE('Test2'[ACT]),
	FILTER(
		ALLEXCEPT('Test2','Test2'[Device]),
		'Test2'[ACT]>0
	)
)
var deviation = CALCULATE(
	STDEV.P('Test2'[ACT]),
	FILTER(
		ALLEXCEPT('Test2','Test2'[Device]),
		'Test2'[ACT]>0
	)
)
return DIVIDE(
	SUM('Test2'[ACT])-mean,
	deviation
)

Hope this helps,

Nathan

[Anonymous]

Thank you, that worked perfectly.