Forum Discussion
Average on a calculated sum
I have done a fair amount of searching in the community and am not finding what I am looking for and am hoping someone can help. Here is my data set: Cycle Time QUOTE_ID Sum Average...
Anonymous,
Try these measures:
Sum Cycle Time = SUMX ( VALUES ( Table1[QUOTE_ID] ), CALCULATE ( SUM ( Table1[Cycle Time] ) ) )Average Cycle Time = VAR vAvg = AVERAGEX ( VALUES ( Table1[QUOTE_ID] ), [Sum Cycle Time] ) VAR vResult = IF ( NOT HASONEVALUE ( Table1[QUOTE_ID] ), vAvg ) RETURN vResultAnonymous,
Try these measures:
Sum Cycle Time = SUM ('CPQ Negotiating'[Cycle Time] ) Mean = CALCULATE ( AVERAGEX ( VALUES ( 'CPQ Negotiating'[QUOTE_ID] ), [Sum Cycle Time] ), ALLSELECTED ( 'CPQ Negotiating'[QUOTE_ID] ) ) Std Dev = CALCULATE ( STDEVX.P ( VALUES ( 'CPQ Negotiating'[QUOTE_ID] ), [Sum Cycle Time] ), ALLSELECTED ( 'CPQ Negotiating'[QUOTE_ID] ) ) Z-Score = DIVIDE ( [Sum Cycle Time] - [Mean], [Std Dev] )I manually entered the first two QUOTE_ID, so Mean, Std Dev, and Z-Score are different from yours. Let me know the result with your full data set.
DataInsights
Super User
Super UserAnonymous,
Try these measures:
Sum Cycle Time = SUM ('CPQ Negotiating'[Cycle Time] )
Mean =
CALCULATE (
AVERAGEX ( VALUES ( 'CPQ Negotiating'[QUOTE_ID] ), [Sum Cycle Time] ),
ALLSELECTED ( 'CPQ Negotiating'[QUOTE_ID] )
)
Std Dev =
CALCULATE (
STDEVX.P ( VALUES ( 'CPQ Negotiating'[QUOTE_ID] ), [Sum Cycle Time] ),
ALLSELECTED ( 'CPQ Negotiating'[QUOTE_ID] )
)
Z-Score = DIVIDE ( [Sum Cycle Time] - [Mean], [Std Dev] )
I manually entered the first two QUOTE_ID, so Mean, Std Dev, and Z-Score are different from yours. Let me know the result with your full data set.
Thank you so much for your help. I had to modify the ALLSELECTED to include some additional columns that are included within the data set but this worked perfectly.