Forum Discussion

Power-User's avatar
Power-User
Frequent Visitor
2 years ago
Solved

Summarise

I am looking for some help to understand how to do what I think is a 3 step process using measures.   This is an example of 1 record in the date source   Date Source ID Positive Marks Neg...
Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago

    Yes, sort of.  You can go easier on the filter, using TREATAS and DATESBETWEEN instead.

lbendlin's avatar
lbendlin
Super User
2 years ago

Unpivot your data to make it usable. Then the measure writes itself.

 

For your second question - you don't provide enough sample data but you would do a SUMMARIZE or SUMMARIZECOLUMNS across the IDs  and then apply a filter.

CEllis's avatar
CEllis
Resolver I
2 years ago

Thanks for the support.

 

Does this look right, I am having trouble getting the dates to filter the SUM

- Table Test =

SUMMARIZECOLUMNS

('Attendance (Table)'[Primary Key],

FILTER(('Attendance (Table)'),

 

AND('Attendance (Table)'[Mark date]>=MIN('Academic Year (Table)'[End of week]),

  'Attendance (Table)'[Mark date]<=MAX('Academic Year (Table)'[End of week]))),

 

"Present",SUM('Attendance (Table)'[Present]),

"Authorised", SUM('Attendance (Table)'[Authorised Absence]),

"UnAuthorised", SUM('Attendance (Table)'[UnAuthorised]),

"Study", SUM('Attendance (Table)'[UnAuthorised]),

"AEA", SUM('Attendance (Table)'[AEA])

)

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    Yes, sort of.  You can go easier on the filter, using TREATAS and DATESBETWEEN instead.

    • Power-User's avatar
      Power-User
      Frequent Visitor
      2 years ago

      I ended up here to produce a table.

       

      -SANDBOX CREATE TABLE =
      SUMMARIZECOLUMNS
      ('Attendance (Table)'[Primary Key],'Academic Year (Table)'[End of week],                      
                               "Present Running Total",
                               CALCULATE (SUM ('Attendance (Table)'[Present]),FILTER (ALL ( 'Academic Year (Table)'[End of week] ),'Academic Year (Table)'[End of week]  <= MAX ( 'Academic Year (Table)'[End of week]))),                     
                              "Auth Running Total",
                              CALCULATE (SUM ('Attendance (Table)'[Authorised Absence]),FILTER (ALL ( 'Academic Year (Table)'[End of week] ),'Academic Year (Table)'[End of week]  <= MAX ( 'Academic Year (Table)'[End of week]))),
                              "UnAuth Running Total",
                              CALCULATE (SUM ('Attendance (Table)'[UnAuthorised]),FILTER (ALL ( 'Academic Year (Table)'[End of week] ),'Academic Year (Table)'[End of week]  <= MAX ( 'Academic Year (Table)'[End of week]))),
                              "Study Leave running total",
                              CALCULATE (SUM ('Attendance (Table)'[Study Leave]),FILTER (ALL ( 'Academic Year (Table)'[End of week] ),'Academic Year (Table)'[End of week]  <= MAX ( 'Academic Year (Table)'[End of week]))),
                              "AEA Running Total",
                              CALCULATE (SUM ('Attendance (Table)'[AEA]),FILTER (ALL ( 'Academic Year (Table)'[End of week] ),'Academic Year (Table)'[End of week]  <= MAX ( 'Academic Year (Table)'[End of week])))
                              )
       
      What I found is I can add a column with the following DAX ok
      % Present (Running) =
      Var Present         =  CALCULATE(SUM('-SANDBOX CREATE TABLE'[Present Running Total]))
      Var Authorised      =  CALCULATE(Sum('-SANDBOX CREATE TABLE'[Auth Running Total]))
      Var UnAuthorised    =  CALCULATE(Sum('-SANDBOX CREATE TABLE'[UnAuth Running Total]))
      Var Study           =  CALCULATE(Sum('-SANDBOX CREATE TABLE'[Study Leave running total]))
      Var AEA             =  CALCULATE(Sum('-SANDBOX CREATE TABLE'[AEA Running Total]))
      Var TotalPresent    = (Present+AEA)+Study // Study leave is converted to a positive mark usually only in term 6
      Var Absent          = (Authorised+UnAuthorised)-Study // Study leave is marked as 'Authorised Absence' by the DfE
      Var TotalMarks      = TotalPresent+Absent
      Return
              IFERROR((TotalPresent/TotalMarks),0)
       
      To get the running % per week
       

       

       

      But if I try to add another column to flag who is below 90% it gives a circular reference.

       

      IF('-SANDBOX CREATE TABLE'[% Present (Running)]<=0.90,1,0)

       

       Any help appreciated

       

       

      • lbendlin's avatar
        lbendlin
        Super User
        2 years ago

        You cannot measure a measure.  Each measure needs to implement its own business logic.

         

        Please provide sample data that fully covers your issue.
        Please show the expected outcome based on the sample data you provided.