Sum column based off of text data

[RyanLMoran]

Hello All:

     Quick question that I think will be relatively easy for sum.  I'm pulling in some data and transformed that data to only show me courses that have the "First Grade" name in them.  That way, I can ensure that I'm getting only the first grade courses and which teachers are teaching those courses.  But, I want to sum that so that I can have a Column that will show me Teacher Emily G is teaching 8 first grade courses.  Just to make it easier to get a quick look at what the load is for courses on that teacher.  This is listing out the courses but I need it summed to a total number.  I'm goinng to do the same thing to have a Kindergarten, Second Grade, Third Grade, etc... total columns for each grade.  That way we can see that Emily is teaching 2 Kindergarten courses,  8 First Grade Courses, 0 Second Grade courses, etc... and then a total in sum at the end to show 22 total elementary courses being taught by her.  Thank you!

[KNP]

There are many ways you can approach this depending on the data and model complexity.

I've attached a simple example PBIX.

Have a look and let me know if you have any questions.

[serpiva64]

Hi, 

please provide not an image of data but some sample data in a format one can use.

[RyanLMoran]

Sure, like the PBIX file or how should I attach that?

[KNP]

Should be easy enough, can you please paste some data to work with? (not a screen shot)

[RyanLMoran]

I'd be happy to.  Would I just attach the PBIX file that I currently have?

[KNP]

If 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?

[RyanLMoran]

Hello:

    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!

[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:

1. 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.
2. 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.

[serpiva64]

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]

For the value "Count of Name" Did you create a new column or field to do the totaling?

[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]

I did see that, thank you.  I'm checking it out now.  And that's what I was wondering as I wasn't sure how to share any data over.  I'm somewhat hesitatant to publicly share it all as it's internal data for a school system:

I transformed the data to only show me the First Grade courses that are active, otherwise the table holds all classes included active and archive

[serpiva64]

I perfectly agree with you on the necessity of not sharing sensitive data.

If you want to share only the data you have just shared you can try this way

selecting the 2 column and With right click Copy

Then you can paste and also decide to keep only the rows you want