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.
Hi everyone,
I have a simple problem but one thats frustrating me. I have school attendance data and I want to show on a table or matrix what the attendance is per year group but when it is aggregated at the bottom in the total it filters out Nursery and Recepetion year group data as this is not counted towards official statistics.
It should look something like this:
Year Group | Attendance |
Nursery | 90% |
Reception | 95% |
Year 01 | 100% |
Year 02 | 100% |
Year 03 | 100% |
Year 04 | 100% |
Year 05 | 100% |
Year 06 | 100% |
Total | 100% |
I thought I had it but this is as close as I came:
Attendance Test1 = VAR __NumberOfYearGroups = DISTINCTCOUNT(Attendance[Year]) VAR __AttendancePerYearGroup = Divide( SUM(Attendance[Attendance]), SUM(Attendance[Sessions])) VAR __AttendanceAggregated = CALCULATE( Divide( SUM(Attendance[Attendance]), SUM(Attendance[Sessions])), NOT(Attendance[Year] IN {"Nursery", "Reception"})) RETURN IF(__NumberOfYearGroups = 1, __AttendancePerYearGroup,__AttendanceAggregated)
The problem is that it aggregates to the correct attendance but it adds all possible year groups and provides the aggregated attendance against those year groups not in the subset as shown in the picture below. What can I change to the meausre to make it both optimised and only return against Year groups with data?
Thanks in advance guys.
Solved! Go to Solution.
Hello @StanleyBlack ,
I think we can get there with an ISINSCOPE and KEEPFILTERS. Give this a try:
Attendance Pct Corrected = IF ( ISINSCOPE ( Attendance[Year] ), DIVIDE ( SUM ( Attendance[Attendance] ), SUM ( Attendance[Sessions] ) ), CALCULATE ( DIVIDE ( SUM ( Attendance[Attendance] ), SUM ( Attendance[Sessions] ) ), KEEPFILTERS ( NOT ( Attendance[Year] IN { { "Nursery" }, { "Reception" } } ) ) ) )
Hello @StanleyBlack ,
I think we can get there with an ISINSCOPE and KEEPFILTERS. Give this a try:
Attendance Pct Corrected = IF ( ISINSCOPE ( Attendance[Year] ), DIVIDE ( SUM ( Attendance[Attendance] ), SUM ( Attendance[Sessions] ) ), CALCULATE ( DIVIDE ( SUM ( Attendance[Attendance] ), SUM ( Attendance[Sessions] ) ), KEEPFILTERS ( NOT ( Attendance[Year] IN { { "Nursery" }, { "Reception" } } ) ) ) )
Worked like a charm. I hadn't come across ISINSCOPE before but off to research it now.
Thanks alot!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |