Forum Discussion
MATRIX
Hi,
I'm trying to use a MATRIX for my dataset. My input data source has data in the following format:
| Date | H_Staff | A_Staff | T_Staff | St_Count |
| 30/05/20 | 1 | 3 | 2 | 34 |
| 30/06/20 | 3 | 7 | 3 | 42 |
| 31/07/20 | 2 | 4 | 4 | 58 |
Myy Power BI Report should look like below:
| Area | Count |
| Housekeeping Staff | 6 |
| Administrative Staff | 14 |
| T_Staff | 9 |
| Student | 134 |
I'm using a text box to display Area, but I cant get count displayed in each rows.
Also, when I try to use a text box and MATRIX, there is white spacing between each and I cant align them properly. Is there a way I can remove the whitespace when using MATRIX and text box?
Any help is greatly appreciated.
Thanks,
AR
Anonymous - Try unpivoting your last 4 columns in Power Query Editor and this this should be very easy.
Hi Anonymous
Just like Greg_Deckler and mahoneypat ‘s reply said Unpivot in Power Query Editor is a good way to achieve your goal.
The way to rename the column header is easy, you can right click the column headers in Power Query Editor or in Fields and choose Rename.
And you may try my way.
I build a table like yours to have a test.
Use raw data to build a matrix.
Then you select Show on rows in Values.
Result:
The result by this way may don’t have column headers, but you can use the original data model to achieve your goal.
And you can build a text box and use group by right-click after selecting both two visuals.
You can download the pbix file from this link: MATRIX
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Rico Zhou
6 Replies
Anonymous - Try unpivoting your last 4 columns in Power Query Editor and this this should be very easy.
Here is some example M code to show you how to unpivot your data to set it up for simple analysis. To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjbQNzDVNzJQ0lEyBGJjIDYC0SZKsToQWTOILEjGHEqbGEFkDfUNzCGyID0mUGxqoRQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, H_Staff = _t, A_Staff = _t, T_Staff = _t, St_Count = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}, {"H_Staff", Int64.Type}, {"A_Staff", Int64.Type}, {"T_Staff", Int64.Type}, {"St_Count", Int64.Type}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date"}, "Attribute", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Staff"}, {"Value", "Count"}}) in #"Renamed Columns"Once you load that table, you just need to make a simple Table visual with the Staff column and the Sum of the Count column to get your desired result.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Anonymous , Unpivot in Power Query
https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/Or in Display use Measure on Row
https://www.burningsuit.co.uk/blog/2019/04/7-secrets-of-the-matrix-visual/
Hi Anonymous
Just like Greg_Deckler and mahoneypat ‘s reply said Unpivot in Power Query Editor is a good way to achieve your goal.
The way to rename the column header is easy, you can right click the column headers in Power Query Editor or in Fields and choose Rename.
And you may try my way.
I build a table like yours to have a test.
Use raw data to build a matrix.
Then you select Show on rows in Values.
Result:
The result by this way may don’t have column headers, but you can use the original data model to achieve your goal.
And you can build a text box and use group by right-click after selecting both two visuals.
You can download the pbix file from this link: MATRIX
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Rico Zhou
