average, std deviation and Z statistic

mitchellericio — Tue, 23 Apr 2024 01:21:51 GMT

Hi Experts,

I am trying to do some analysis on volumes per headcount on our data. Put in the DAX measures but somehow it is not agreeing with the manual formula checks am doing. Currently the data model is in Power Pivot in excel.

Here is a snipshot of the pivot and the manual computation I did( below the pivot).

DAX formulas:

Quotes / HC:=CALCULATE(

DIVIDE(

'Measures_Quotes Created'[Quotes_Created],

HeadCountMeasures[Total_NonSupHC],

YEAR(Months[Month]) = 2023

)

Avg_Quotes/HC:=CALCULATE(

DIVIDE(

SUMX(

'Volume Table',

DIVIDE(

'Volume Table'[Sum of Volume],

HeadCountMeasures[Total_NonSupHC],0)

DISTINCTCOUNT(Months[Month]),

// Implement Filtering

ALL(Months[Month]),

YEAR(Months[Month]) = 2023,

'Volume Table'[Attribute] = "Sum of Quotes Created"

)

StdDev_Avg_Quotes/HC:=CALCULATE(

STDEVX.S(

'Volume Table',

DIVIDE(

'Volume Table'[Sum of Volume],

HeadCountMeasures[Total_NonSupHC],

)

// Implement Filtering

ALL(Months[Month]),

YEAR(Months[Month]) = 2023,

'Volume Table'[Attribute] = "Sum of Quotes Created"

)

zQuotes/HC:=CALCULATE(

DIVIDE(

'Measures_Quotes Created'[Quotes_Created],

HeadCountMeasures[Total_NonSupHC],

) - zMeasures_Quotes_Created[Avg_Quotes/HC],

zMeasures_Quotes_Created[StdDev_Avg_Quotes/HC],

)

The data model consist of a Volume table, a headcount table and the months (date) table.

Hope you can help me.

Re: average, std deviation and Z statistic

Anonymous — Wed, 24 Apr 2024 06:28:48 GMT

Hi mitchellericio ,

Are you referring to customizing the values based on the Column of the matrix, which you can do with the Switch function.

Measure = SWITCH( TRUE(), MAX('Table'[Manual])="Zstat",1, MAX('Table'[Manual])="Quote/HC",2, MAX('Table'[Manual])="AvgQuotes/HC",3, MAX('Table'[Manual])="Zstat",4)

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

topic Re: average, std deviation and Z statistic in DAX Commands and Tips

average, std deviation and Z statistic

Re: average, std deviation and Z statistic