Forum Discussion
Trending Metrics and Grouping
I'm trying to decide what is the best way to achieve the following:
Table I am trying to reproduce in PBI
| This col is info only | Continue | Switch | Leave | Total | Program Retention | College Retention | Left the College | |||||
| Year | 2020 | |||||||||||
| Program | Early Childhood Education | 175 | 3 | 15 | 193 | 91% | 92% | 8% | ||||
| School | Community Services | 672 | 22 | 115 | 809 | 83% | 86% | 14% | ||||
| College | College | 7,101 | 509 | 1,100 | 8,710 | 82% | 87% | 13% | ||||
| 2021 | ||||||||||||
| Early Childhood Education | 161 | 10 | 12 | 183 | 88% | 93% | 7% | |||||
| Community Services | 600 | 21 | 96 | 717 | 84% | 87% | 13% | |||||
| College | 7,543 | 490 | 1,412 | 9,445 | 80% | 85% | 15% |
And a graph similar to the one below
My data is at the program level and contains the School to which it belongs. A School is comprised of many programs and the College is all programs.
METRICS:
Program retention = Continue/Total
College retention = (Continue+Switch)/Total
Left = Leave/Total
The end user selects a program, and I want to display the data for the selected program and the comparator data, which is a total for all programs in the School and a total for the overall College for each year. In the example above the program is 'Early Childhood Education' and the School is 'Community Services'.
I'm a newbie when it comes to DAX. My question is, is it better to create summary tables (one for the Schools and one for the College overall) and join to the detailed program level OR create measures? If I were to create measures, how would I aggregate the totals for the School and College levels?
We do MANY trending reports of this type. I'm trying to avoid having to create summary tables every time if there is a DAX solution.
Thank you for any assistance you can provide.