Forum Discussion
Helper ISum column based off of text data
Super UserSo if you go into Power Query (Transform Data) in the PBIX I attached, and look at the steps, you can see I added a custom column prior to pivoting. It is simply just a repeating 1 to give you something to sum on.
To deal with 'Kindergarten' you'd also need to alter the split, as it is currently splitting on " grade ", change it to " " << the first space.
Again, I'd be able to offer a more definitive answer with some sample data.
Helper Ihttps://drive.google.com/file/d/1YxXx69jHEV-G69UB8flojNF85bbVFEvD/view?usp=sharing
This should link you to a copy of the report. Thank you!
- serpiva64
Solution Sage
Hi, if you want something like this (i duplicated the rows only changing in Name the grade):
You can add column with extract
then
add a conditional column
Then on the cnvas you only need
As you see it is not necessary to create many query (one for each grade), the filter on ACTIVE can semplify but you can also gain the same result with a slicer in the canvas
If this post isuseful to help you to solve your issue consider giving the post a thumbs up and accepting it as a solution !
- RyanLMoran
Thanks! I'll dive into this tonight, this looks to be along the lines of what I'm trying to get for output. And, you're right, it is more efficient than trying to create a query for each elementary grade
- RyanLMoran
For the value "Count of Name" Did you create a new column or field to do the totaling?
- KNP
Hi 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.
- RyanLMoran
This 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.
