Cumulative sums

[davidwsw]

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.

[Sean]

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]

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

[v-qiuyu-msft]

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 · Accepted solution]

Hi @davidwsw,

Did the DAX in my orignal post meet your requirement?

Best Regards,
Qiuyun Yu