Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Z-Score Calculation with Multiple Filters

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!

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      Thank you, that worked perfectly.

  • parry2k's avatar
    parry2k
    Super User
    7 years ago

    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's avatar
    Anonymous
    Not applicable
    7 years ago

    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