Forum Discussion
Helper ISum column based off of text data
Super UserHi RyanLMoran,
I've attached your PBIX (with some changes).
I can't update it as it has direct connections to Dataflows so I had to manually duplicate the tables as 'F' and 'T' to test. You can delete those.
Because I can't update it, the new column I added, 'Level', won't show until you open and update it.
Once you apply changes after opening, drag the new 'Level' field into the Columns for the bottom left pivot table.
Let me know if this works for you.
Helper IThis has a lot of the optionality that I'm looking for. But, it's giving me all courses, where I need it to only show me the elementary courses, Kindergarten through Sixth Grade. I don't think that creating a filter multiple times over is the best path forward i.e. one for Kindergarten, one for First Grade, and then try and sum them. I'm trying to get it to output like:
Elementary Teacher by Grade Level Report | |||||||
Teacher names | Grade K | Grade 1 | Grade 2 | Grade 3 | Grade 4 | Grade 5 | Total |
Teacher 1 |
|
|
|
|
|
|
|
Teacher 2 |
|
|
|
|
|
|
|
Teacher 3 |
|
|
|
|
|
|
|
Total |
|
|
|
|
|
| Total |
Total Enrollments by Grade Level | |
Grade Level | Total Number of enrollments |
Kindergarten |
|
First |
|
Second |
|
Third |
|
Fourth |
|
Fifth |
|
- KNP
This is the trouble with only having one example of the data to work with, without all scenarios.
Couple of options to deal with this:
- Add a filter in power query (like the one you had originally for contains 'first grade') to only include the grades you're interested in. e.g. Contains 'first grade' or Contains 'second grade' etc.
- Add a report level or page level filter to the report. Use the 'Level' field I created. See gif below. Then select the grades you care about.
The best way to do it would be to create a dimension that had grade and their parent category and then filter that way but that's a different conversation.