Forum Discussion
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...
Yes, sort of. You can go easier on the filter, using TREATAS and DATESBETWEEN instead.
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
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.