Formatting a power bi matrix table

[afleiderman1]

I am wondering is there a way to format a power bi report I have the following type

of chart 

0-grocery                                             2-dairy                                                                      3-frozen

actual  predicted picked                  actual   predicted   picked                               actual      predicted picked 

I have my column are the store which incldue  [0-grocery,2-dairy,3-frozen ]

and my values are actual predicted and pick which are column values

But want is to have something like this

ACTUAL                                                                    PREDICTED                                                      PICKED

0-grocery  2-dairy  3-frozen                       0-grocery   2-dairy    3-frozen                      0-grocery    2-dairy      3-frozen

 100           120          130                       90                   80              30                                   90              20           40 

Not sure how I could get something like this.                

[afleiderman1]

I guess my issue is the way my data is set up

I have my columns are like this

for example

Facility                       Actual Prediction Picked

0-grocery                   100     200          90 

2-dairy                      90        21          222 

I am not sure how to get a type column to distinguish them. 

[v-yiruan-msft · Accepted solution]

Hi @afleiderman1 ,

I created a sample pbix file(see the attachment), please check if that is what you want. You can find the details in the attachment.

1. Handle with it in Power Query Editor:

• Select the column [Actual], [Prediction] and [Picked] and unpivot these selected columns
• Add one custom column [Index] 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtBNL8pPTi2qVNJRMjQwAJJGYNLSQClWJ1rJSDclMbOo8tACiBBQ1hBEGBkpxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Facility = _t, Actual = _t, Prediction = _t, Picked = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Facility", type text}, {"Actual", Int64.Type}, {"Prediction", Int64.Type}, {"Picked", Int64.Type}}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"Actual", "Prediction", "Picked"}, "Type", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Only Selected Columns", "Index", each if [Type]="Prediction" then 1 else if [Type]="Actual" then 2 else 3)
in
    #"Added Custom"

2. Sort the column [Type] by the column[Index], then create a matrix visual with the below settings

Best Regards

[v-yiruan-msft]

Hi @afleiderman1 ,

You can create a matrix visual as below with the below setting as below screenshot:

If the above one can't help you, could you please provide more raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards