Forum Discussion

RyanLMoran's avatar
RyanLMoran
Icon for Helper I rankHelper I
4 years ago

Sum column based off of text data

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. ...
Need Help
RyanLMoran's avatar
RyanLMoran
Icon for Helper I rankHelper I
4 years ago

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

KNP's avatar
KNP
Icon for Super User rankSuper User
4 years ago

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's avatar
    RyanLMoran
    Icon for Helper I rankHelper I
    4 years ago

    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's avatar
      serpiva64
      Icon for Solution Sage rankSolution Sage
      4 years ago

      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

  • RyanLMoran's avatar
    RyanLMoran
    Icon for Helper I rankHelper I
    4 years ago

    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's avatar
      KNP
      Icon for Super User rankSuper User
      4 years ago

      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.