Forum Discussion
Helper ISum column based off of text data
Super UserIf the data is not sensitive, yes. You'll have to share a link to google drive or similar as attaching files is restricted. Alternatively, you could just paste some sample data directly.
Did you see the PBIX I attached earlier? Does this help?
Helper IHello:
The sample report you sent over is essentially what I'm looking for. The first column will list the teacher name, then across the top will list Kindergarten, first grade, second grade, etc... with the row totaling the enrollments in each grade for the teacher and then at the bottom giving a total. But, I'm not sure how I sum and total based on the text string as shown in the data that I have. And to lay it out like you have it. Thanks, I appreciate the help.
- KNP
So 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.
- RyanLMoran
https://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 !
