Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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...
Need Help
  • DataInsights's avatar
    DataInsights
    4 years ago

    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
    vResult

     

     

  • DataInsights's avatar
    DataInsights
    4 years ago

    Anonymous,

     

    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's avatar
DataInsights
Icon for Super User rankSuper User
4 years ago

Anonymous,

 

Would you be able to provide the expected result, along with the calculation logic?

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Ultimately what I am trying to do is a Z-score based on the summed values so:

ZScore = ([Sum of Cycle Time]-[Mean])/[Standard Deviation]
 
 
Here is an example of what it looks like without the sum (it is going against the individual values which I don't want):
Here is what the Zscore calculation looks like against the raw data (without cycle time summed):

Raw data

This is what I am trying to do:

So the mean is based on the average of the summed amounts, the standard deviation also goes against the sum of the cycle time. 

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    4 years ago

    Anonymous,

     

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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.