Counting Values to use on rows of matrix

[heidibb]

Hello Community!

I'm hoping you can help with this. I have a matrix created (picture attached). What I would like to do is count the number of cells that are populated for each person (row) and add that count to the rows.

So my rows would be Name, Start Date, # Courses Taken

If we're looking at the first row of my picture, that value would be 10. The second row would be 4. I would also like to be able to sort on that field as well so we're showing the people that have taken the most courses at the top.

Possible??

[BobBI]

Hi,

Create a similar data set for easy reference. table name = Matrix

Step1  : select all columns ( 1-14 ) where course name mentions --> unpivot them in query editor. ( see the screenshot after unpivot of data)

step2 : added a coulumn called 'Course' = if(Len(Value)>0,1," ")   - you can poluate 1 or 0 if you like ( idea is to calculate sum of this column)

Step3: create a measure 'Number of Course' =  sum(Matrix[course])

Drag Name and Number of couse in the table to verify value and play around to create chart of any report.here below is result

Hope this helps.

Bob.

[heidibb · Accepted solution]

Thank you so much!

I used your tip to duplicate the data set, but I did it slightly differently. After a few steps, my matrix data set ended up being the person's ID and the sum of the courses taken so I had one unique row per person. Then I could join that "matrix" data set to my main data set and pull that sum of courses taken into my view so I could group and sort on those that have takein the most/least courses. I ultimately needed that # placed into my existing matrix.

[BobBI]

Great , i wouldn't expect this to exactly same as i did, but more to give an idea to achieve your desire result 🙂

are you able to delivery your report . please post your question if you still have any.

Have a good day.