average, std deviation and Z statistic

[mitchellericio]

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

Pivot table

DAX formulas:

Quotes / HC:=CALCULATE(

DIVIDE(

'Measures_Quotes Created'[Quotes_Created],

HeadCountMeasures[Total_NonSupHC],

0

),

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]),

0

),

// 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],

0

)

),

// Implement Filtering

ALL(Months[Month]),

YEAR(Months[Month]) = 2023,

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

)

zQuotes/HC:=CALCULATE(

DIVIDE(

DIVIDE(

'Measures_Quotes Created'[Quotes_Created],

HeadCountMeasures[Total_NonSupHC],

0

) - zMeasures_Quotes_Created[Avg_Quotes/HC],

zMeasures_Quotes_Created[StdDev_Avg_Quotes/HC],

0)

)

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

Hope you can help me.

[Anonymous]

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.