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 calculated for each user in each category based on how many correct answers he got in that category.

 

 

 

How can I calculate cumulative sums for CorrectAnswers and QuestionsAnswered by date, by user and by question category? I want to use the cumulative sums to caculate a cumulative score. That part is easy. It's the cumulative sums that I'm struggling with. This is the end result I'm looking for.

 

 

 

Need Help

4 Replies

  • 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