A step by step guide:
Convert Input to Table, and import to PQ (with headers)
When PQ opens, copy the query so you have Table1 and Table2
For Table1- remove 'vendorname' column
- group by model, segment and brand, with new column 'Count' as the sum of Units
For Table2- go to Combine > Merge queries
- select Table1 from the dropdown, then holding down control, click Model, Segment and Brand for both tables, and do an inner join
- expand Table1, but Remove all columns except 'Table1.Count'
- add a custom column, which is simply = Units / Table1.Count, rename as '%Result" then transform the datatype to %
- Remove 'Units' and 'Table1.Count' columns
- select the 'VendorName' column and go to transform > pivot column > values column = '%Result'
- close and load the query
There's probably a method to calculate this also within one query, but I find this approach the easiest to follow
Bookmarks