Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Formatting a power bi matrix table

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.                

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

    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:

    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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Anonymous ,

      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:

      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