Forum Discussion

CEllis's avatar
CEllis
Resolver I
1 year ago
Solved

Matrix - Reset Cumulation

I've been restling with this for a few days now and cant seem to get it right.

 

I am using a dax statment to provide a cummulation figure in a matrix that works.

 

CALCULATE(SUM('Attendance (Table)'[Present]),
    FILTER(
            ALL('Academic Year (Table)'),
             'Academic Year (Table)'[Week Ending]<= MAX('Academic Year (Table)'[Week Ending])))

 

 

The next step was to  - Check to see
if the Total Marks = Present then continue 

 

CALCULATE(SUM('Attendance (Table)'[Present]),
FILTER(
ALL('Academic Year (Table)'),
'Academic Year (Table)'[Week Ending]<= MAX('Academic Year (Table)'[Week Ending])))


Then to check if they had reached the threshold 

 

if the Cumulation reached 21 then reset the Cumulation to 0

 

Any help greatly appreciated.

 

 

 

 

 

 

 

 

 

Need Help
  • AlexisOlson's avatar
    AlexisOlson
    1 year ago

    I don't fully understand what you're trying to do but you could try using MOD.

    VAR _Present =
    CALCULATE (
        SUM ( 'Attendance (Table)'[Present] ),
        FILTER (
            ALL ( 'Academic Year (Table)' ),
            'Academic Year (Table)'[Week Ending]
                <= MAX ( 'Academic Year (Table)'[Week Ending] )
        )
    )
RETURN
    MOD ( _Present, 21 )

     

2 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    1 year ago

    I don't fully understand what you're trying to do but you could try using MOD.

    VAR _Present =
    CALCULATE (
        SUM ( 'Attendance (Table)'[Present] ),
        FILTER (
            ALL ( 'Academic Year (Table)' ),
            'Academic Year (Table)'[Week Ending]
                <= MAX ( 'Academic Year (Table)'[Week Ending] )
        )
    )
RETURN
    MOD ( _Present, 21 )

     

    • CEllis's avatar
      CEllis
      Resolver I
      1 year ago

      AlexisOlson Thanks for the respone.

       

      The outcome I am looking for is to flag at the point it hits 21 and then continue the count from 0 to see whn the next 21 occures.

       

      I do appreciate your help with this.

       

      Week Ending  Fri 06 Sep  Fri 13 Sep  Fri 20 Sep  Fri 27 Sep  Fri 04 Oct  Fri 11 Oct  Fri 18 Oct
      AnonTotal MarksPresentCumulativeTotal MarksPresentCumulativeTotal MarksPresentCumulativeTotal MarksPresentCumulativeTotal MarksPresentCumulativeTotal MarksPresentCumulativeTotal MarksPresentCumulative
      Pupil 8972860101010104088810801010101080
      Pupil 90788601010101010208001000101010101020
         if Total Marks = Present Sum(Present) else 0 or  if Total Marks = Present Sum(Present) else 0 or  if Total Marks = Present Sum(Present) else 0 or  if Total Marks = Present Sum(Present) else 0 or  if Total Marks = Present Sum(Present) else 0 or  if Total Marks = Present Sum(Present) else 0 or  if Total Marks = Present Sum(Present) else 0 or
         if Total Marks =21 then 21  if Total Marks =21 then 21  if Total Marks =21 then 21  if Total Marks =21 then 21  if Total Marks =21 then 21  if Total Marks =21 then 21  if Total Marks =21 then 21