Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Power-User
Frequent Visitor

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
IDPositive MarksNegative Marks 1Negative Marks 2Negative Marks 3
11000
11000
10100
11000
11000
10010
10010
11000
10100
10001
10001

 

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

 

Summarised 
IDPositive MarksNegative Marks 1Negative Marks 2Negative Marks 3Total Marks% of Positive Marks
152221145.5%

 

Count all records that are above a particular %

 

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

 

Many thanks

1 ACCEPTED SOLUTION

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

View solution in original post

5 REPLIES 5
lbendlin
Super User
Super User

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

 

lbendlin_0-1698515983870.png

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.

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])

)

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
 PowerUser_0-1699526093144.png

 

 

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)
PowerUser_1-1699526238238.png

 

 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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.