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