Forum Discussion

ianwuk's avatar
ianwuk
Icon for Helper III rankHelper III
8 years ago
Solved

How can I recreate this table in Power Bi?

Hello.

 

I have a table that looks like this (this is a sample).

 

Staff  Orders  Sales        Commission      Refunds      Amount Refunded

Chris    23       $1000       $500                    3                    $114

Jane     7         $600         $50                     1                     $50

 

Commission is worked out as 0.3 * Sales value.  I can add that as a custom column.

 

My question is mainly about grouping.  If I can ouput all individal orders made by both members of staff how can I group that data to look like the above?

 

For example, the main table may look like this

 

Order Date   Staff       Product         Amount

1/1/18           Jane    Hammers        $50

1/1/18           Jane    Pliers               $100

2/1/18           Chris    Hammers       $100

 

How can I group the main table to look like the first table above and for a week only at a time? (e.g. Jan 28th - Feb 10th) 

 

So, group by staff (which should group by produt and amount too) and then sum the amount for each staff and call it Sales and then group that by weeks also.

 

Hope that makes sense.

 

Many thanks.

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    ianwuk,

    Please test the following code.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdOxCoNADAbgd7lZMMmdZzp3KZ26i4ODUKF20PeHWqSxmIvepMPHn+hPmsZRiVgSILvC3bt3vzxu3Tj207y8VeDaImker2ElCGK8aUhMsEwtpDLXiWKiaVhMbY0KQtgi8uGXjGXgfJBJvEm2SZRhthz6metzGub0Org3WUGpjXxO0gGKuna1N+vadVDQvaugqIvXQayqPxoG5rC/w1C/O3UZdm/f02g/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Order Date" = _t, Staff = _t, Product = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order Date", type date}, {"Staff", type text}, {"Product", type text}, {"Amount", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Week", each Date.WeekOfYear([Order Date])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each if [Order Date]>=#date(2018,2,11) and [Order Date]<=#date(2018,2,18) then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom] = 1)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Staff"}, {{"Order", each Table.RowCount(_), type number}, {"Sales", each List.Sum([Amount]), type number}}),
    #"Added Custom2" = Table.AddColumn(#"Grouped Rows", "specific week", each "11th February 2018 - 18th February 2018")
in
    #"Added Custom2"



    Regards,
    Lydia

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    ianwuk,

    Add two blank queries in your Power BI Desktop, paste the following code to the Advanced Editor or the two queries, then check if you get expected result.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZMxC4MwFIT/S2bBvCTGOHcpnbqLg4NQoTro/4da5CXg5amTGT7uzjte2yoqqTSagirUq5+H7fPsp2lY1u1VadUVOeT9HXeCNCNGRAwjVkJqJpwYxTNSiUhgxEs+jolaIuIfh+skzaUNaQmxEZHbj73RsdvEWKj/8VnGNZ/miNyRycSxN3ROGFwSIuOSKINbgoyHMVEmwJonVmlP8EqHQNBz5hRI3ut/DN0P", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Order Date" = _t, Staff = _t, Product = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order Date", type date}, {"Staff", type text}, {"Product", type text}, {"Amount", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Week", each Date.WeekOfYear([Order Date])),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Staff", "Week"}, {{"Orders", each Table.RowCount(_), type number}, {"Sales", each List.Sum([Amount]), type number}})
in
    #"Grouped Rows"



    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZMxC4MwFIT/S2bBvCTGOHcpnbqLg4NQoTro/4da5CXg5amTGT7uzjte2yoqqTSagirUq5+H7fPsp2lY1u1VadUVOeT9HXeCNCNGRAwjVkJqJpwYxTNSiUhgxEs+jolaIuIfh+skzaUNaQmxEZHbj73RsdvEWKj/8VnGNZ/miNyRycSxN3ROGFwSIuOSKINbgoyHMVEmwJonVmlP8EqHQNBz5hRI3ut/DN0P", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Order Date" = _t, Staff = _t, Product = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order Date", type date}, {"Staff", type text}, {"Product", type text}, {"Amount", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Week", each Date.WeekOfYear([Order Date])),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Staff", "Week", "Product"}, {{"Orders", each Table.RowCount(_), type number}, {"Sales", each List.Sum([Amount]), type number}})
in
    #"Grouped Rows"



    Regards,
    Lydia

    • ianwuk's avatar
      ianwuk
      Icon for Helper III rankHelper III
      8 years ago

      Hello.

       

      Thanks for replying Anonymous.

       

      How can I output it so that once grouped, it looks something like this?

       

                                     Week                                         Staff    Orders   Sales

        11th February 2018 - 18th February 2018             Jane     35         1200

        11th February 2018 - 18th February 2018             Peter    12         750

       

      Basically, it adds up all the Orders numbers and the Sales numbers and just displays the totals for each staff member for the week specified (Sunday to Sunday)?

       

      How can I do this please?

      Thanks.

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        ianwuk,

        Please verify that if you want to add all sales of all dates per staff,  and just add a week column as 2/11/2018-2/18/2018 or if you want to add all sales of dates from  2/11/2018 to 2/18/2018  per staff?

        Regards,
        Lydia