Well you have a few options here -- you could create four separate pivot tables so you can report each brand separately rather than trying to create four new worksheet tables...

if you didn't want to use PT and assuming you could use helper columns on your master sheet...

Assume screenshot (master data) is on Sheet1 but let's assume headers in row 1 and data begins in row 2

Formula in H2 (to be copied down for all rows with data -- my example assume 1000 rows)

="o"&$A2&":c"&B2

Formula in I2 (to be copied down for all rows with data -- my example assume 1000 rows)

=IF(COUNTIF($H$1:$H2,$H2)>1,"",$G2)

Formula in J2 (to be copied down for all rows with data -- my example assume 1000 rows)

=IF($I2="","",$I2&COUNTIF($I$1:$I2,$I2))

Now let's assume you have created a new sheet and called it A (for Brand A)

On sheet A in cell A1 type A

in B1 to G1 type your column headers (Order Number, Customer etc...)

Still on sheet A

Formula in A2 (to be copied down for all rows with data -- my example assume 500 rows)

=MATCH($A$1&ROW(A2)-1,Sheet1!$J$1:$J$1000,0)

Formula in B2 (to be copied down for all rows with data -- my example assume 500 rows)

=IF(ISERROR($A2),"",INDEX(Sheet1!$A$1:$G$1000,$A2,1))

Formula in C2 (to be copied down for all rows with data -- my example assume 500 rows)

=IF(ISERROR($A2),"",INDEX(Sheet1!$A$1:$G$1000,$A2,2))

Formula in D2 (to be copied down for all rows with data -- my example assume 500 rows)

=IF(ISERROR($A2),"",SUMIF(Sheet1!$H$2:$H$1000,"o"&$B2&":c"&$C2,Sheet1!C$2:C$1000))

Formula in E2 (to be copied down for all rows with data -- my example assume 500 rows)

=IF(ISERROR($A2),"",SUMIF(Sheet1!$H$2:$H$1000,"o"&$B2&":c"&$C2,Sheet1!D$2:D$1000))

Formula in F2 (to be copied down for all rows with data -- my example assume 500 rows)

=IF(ISERROR($A2),"",SUMIF(Sheet1!$H$2:$H$1000,"o"&$B2&":c"&$C2,Sheet1!E$2:E$1000))

Formula in G2 (to be copied down for all rows with data -- my example assume 500 rows)

=IF(ISERROR($A2),"",SUMIF(Sheet1!$H$2:$H$1000,"o"&$B2&":c"&$C2,Sheet1!F$2:F$1000))

You know have a sheet for Brand A showing only Brand A details grouped at invoice level.

Copy sheet A and rename B -- change B1 to be B and you should have a sheet for B... repeat for C & D.