Forum Discussion
Creating a Matrix based on the Column Values Separated by Comma
Yes, insert the new column in the same table where you split the rows. The value in Country will copy to the new rows after you split the column, so it should not affect the values.
One approach is to split the column Accredited By into rows. After this step, create a Trim step to remove spaces.
Since the above step will result in the EM Enrolment Calculated amount being copied to each row, create a custom column in Power Query using the expression below. It divides the original amount in EM Enrolment Calculated by the number of values in Accredited By. This will enable you to sum this column in a measure and get the correct amount.
[EM Enrolment Calculated] / List.Count(Text.Split([Accredited By], ","))
For the Schools measure, you can use DISTINCTCOUNT.
Hi, thanks for the assistance. I'd like to ask where should I insert the new column? On the same table where I split my rows?
note that i'll also filter it further by country. Would that affect my values?
Yes, insert the new column in the same table where you split the rows. The value in Country will copy to the new rows after you split the column, so it should not affect the values.
Hi DataInsights, how do I show the enrolment in the matrix? Note that I am also using enrolment data age for columns and I'd like to also include the EM Enrolment Calculated in there
Create a measure that sums the custom column created in Power Query. This new column should be used instead of [EM Enrolment Calculated].