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.

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.