+ Reply to Thread
Results 1 to 3 of 3

Help with Power Query to calculate the % sales contribution in units at a model/seg/Brand

  1. #1
    Forum Contributor
    Join Date
    10-18-2016
    Location
    Chicago, USA
    MS-Off Ver
    Office 365
    Posts
    220

    Red face Help with Power Query to calculate the % sales contribution in units at a model/seg/Brand

    Hi All,

    Hope you are doing well!..I am trying to calculate the percentage contribution of sales for different vendors for a unique combination of segment, model and Brand for different vendors...Please find attached the file with the input and the expected output ...Can you please help me here..


    Thanks,
    Arun
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    01-07-2022
    Location
    Europe
    MS-Off Ver
    Office 365
    Posts
    473

    Re: Help with Power Query to calculate the % sales contribution in units at a model/seg/Br

    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
    <<< If you have valued anyone's contributions in this thread, please click * to thank them for their efforts

  3. #3
    Forum Contributor
    Join Date
    10-18-2016
    Location
    Chicago, USA
    MS-Off Ver
    Office 365
    Posts
    220

    Re: Help with Power Query to calculate the % sales contribution in units at a model/seg/Br

    Thank you so much!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 14
    Last Post: 01-11-2022, 10:55 AM
  2. Replies: 1
    Last Post: 07-17-2021, 02:40 AM
  3. Can't add power query to data model
    By yansong888 in forum Excel General
    Replies: 1
    Last Post: 11-14-2019, 09:18 PM
  4. Power Query makes Excel crash - can't access Data Model
    By phil34 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-10-2018, 01:01 PM
  5. Replies: 7
    Last Post: 07-25-2017, 08:38 AM
  6. Replies: 1
    Last Post: 11-19-2013, 12:52 PM
  7. Sales Report: Percentual contribution of each client's vertical to overall sales
    By panther98 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-22-2013, 07:07 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1