Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Summing text columns for visual

Hi,

 

I'm hoping that I'm overlooking the simple solution here - but what's the best approach to sum up text values for a visual?

 

I have a set of data that includes 8 different skill types, which I need to condense into a single visual (small sample below). I'd like the value (ie, improved, developed, no change) along the X-axis, and the count of each skill on the Y axis. Is there a way to do this that doesn't require calculated columns or dax functions for each?

 

Skill - collaborationSkill - planningSkill - leadershipSkill - presentingSkill - technical
improvedimproveddevelopedno changeimproved
developedno changeimproveddevelopeddeveloped
improveddevelopedno changedevelopedimproved
no changedevelopeddevelopedimproveddeveloped
developedimprovedno changeno changedeveloped

 

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi Anonymous ,

    According to your description, here's my solution.

    In PowerQuery, select all columns and click Unpivot, then rename the columns, get table like this:

    In Desktop, put States in Axis, Skill in Legend and Values. In the Values well, select Count, get the result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

6 Replies

  • truptis's avatar
    truptis
    Community Champion
    4 years ago

    Hi Anonymous ,

    Try doing Pivotting/Unpivotting so that you get Improved, Developed and no change in column and the types of skills in rows. After that put values on the X-axis and types of skills in Y axis.

     

    If this helps you then mark it as solution and hit the thumbs up. Thanks.

     

    Regards,

    Trupti S

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      I'm struggling to get this to work - could you explain further?

      I'm able to unpivot my 8 columns so that I get an attribute-value pair, but can't seem to see how to re-pivot it to get my 3 values as column heads and my skills as rows

       

      Thanks

       

  • PC2790's avatar
    PC2790
    Community Champion
    4 years ago

    Hey Anonymous ,

     

    How does your sample data look like?

    If the table provided i your original post the expected outcome?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi

       

      I'm not sure I follow you? The table in my original post is what this section of my data looks like, but the outcome desired is a graph

       

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    Hi Anonymous ,

    According to your description, here's my solution.

    In PowerQuery, select all columns and click Unpivot, then rename the columns, get table like this:

    In Desktop, put States in Axis, Skill in Legend and Values. In the Values well, select Count, get the result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.