Summarise

[Power-User]

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	Negative Marks 1	Negative Marks 2	Negative Marks 3
1	1	0	0	0
1	1	0	0	0
1	0	1	0	0
1	1	0	0	0
1	1	0	0	0
1	0	0	1	0
1	0	0	1	0
1	1	0	0	0
1	0	1	0	0
1	0	0	0	1
1	0	0	0	1

This is what I am looking at summarising from the date source then use to create the finall output

Summarised
ID	Positive Marks	Negative Marks 1	Negative Marks 2	Negative Marks 3	Total Marks	% of Positive Marks
1	5	2	2	2	11	45.5%

Count all records that are above a particular %

Count the number of % that are greater than 40%
	Count
Finall Output	1

Many thanks

[lbendlin]

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]

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

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

[Power-User]

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]

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.