MATRIX

[Anonymous]

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

[Greg_Deckler · Accepted solution]

@Anonymous - Try unpivoting your last 4 columns in Power Query Editor and this this should be very easy.

[Anonymous · Accepted solution]

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

[Anonymous]

Hi @Anonymous , That worked perfect.

In the output displayed, if I need to display one count as %, (say for example H_Count), how do I do that?

Thanks,

ara

[amitchandak]

@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/

[Greg_Deckler · Accepted solution]

@Anonymous - Try unpivoting your last 4 columns in Power Query Editor and this this should be very easy.

[Anonymous]

Thanks Greg. Where do I then rename rows? Do I rename using DAX if statements?

[mahoneypat]

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