Forum Discussion

davidwsw's avatar
davidwsw
Frequent Visitor
9 years ago
Solved

Cumulative sums

I have a table that tracks users' performance in various categories of questions as they take a quiz. 5 questions are drawn randomly from 3 different question categories X, Y and Z. A score is calcul...
Need Help
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    9 years ago

    Hi davidwsw,

     

    Did the DAX in my orignal post meet your requirement?

     

    Best Regards,
    Qiuyun Yu

Sean's avatar
Sean
Community Champion
9 years ago

Do you want these as COLUMNS or MEASURES?

 

COLUMNS

 

Score = 'Table'[CorrectAnswers] / 'Table'[QuestionsAnswered]

CumulativeCorrectAnswers =
CALCULATE (
    SUM ( 'Table'[CorrectAnswers] ),
    ALLEXCEPT ( 'Table', 'Table'[User], 'Table'[QuestionCategory] ),
    'Table'[Date] <= EARLIER ( 'Table'[Date] )
)

CumulativeQuestionsAnswered =
CALCULATE (
    SUM ( 'Table'[QuestionsAnswered] ),
    ALLEXCEPT ( 'Table', 'Table'[User], 'Table'[QuestionCategory] ),
    'Table'[Date] <= EARLIER ( 'Table'[Date] )
)

CumulativeScore = 'Table'[CumulativeCorrecAnswers] / 'Table'[CumulativeQuestionsAnswered]
davidwsw's avatar
davidwsw
Frequent Visitor
9 years ago

Sean Either as long as I can show them in a table visualization. Thanks.

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Community Support
    9 years ago

    Hi davidwsw,

     

    In your scenairo, you can create measures like below and drag them into the table visual:

     

    CumulativeCorrectAnswers = CALCULATE(SUM( Sheet1[CorrectAnswers]),FILTER(ALL(Sheet1),Sheet1[Date]<=MAX(Sheet1[Date])),VALUES(Sheet1[User]), VALUES(Sheet1[QuestionCategory]))

     

    CumulativeQuestionsAnswered = CALCULATE(SUM( Sheet1[QuestionsAnswered]),FILTER(ALL(Sheet1),Sheet1[Date]<=MAX(Sheet1[Date])),VALUES(Sheet1[User]), VALUES(Sheet1[QuestionCategory]))

     

    CumulativeScore = CALCULATE(SUM( Sheet1[CorrectAnswers]),FILTER(ALL(Sheet1),Sheet1[Date]<=MAX(Sheet1[Date])),VALUES(Sheet1[User]), VALUES(Sheet1[QuestionCategory]))/CALCULATE(SUM( Sheet1[QuestionsAnswered]),FILTER(ALL(Sheet1),Sheet1[Date]<=MAX(Sheet1[Date])),VALUES(Sheet1[User]), VALUES(Sheet1[QuestionCategory]))

     

     

     

    Best Regards,
    Qiuyun Yu

    • v-qiuyu-msft's avatar
      v-qiuyu-msft
      Community Support
      9 years ago

      Hi davidwsw,

       

      Did the DAX in my orignal post meet your requirement?

       

      Best Regards,
      Qiuyun Yu