+ Reply to Thread
Results 1 to 3 of 3

Calculating weighted average by category in Power Query (or Power Pivot)

  1. #1
    Registered User
    Join Date
    08-12-2007
    Location
    United Sates
    MS-Off Ver
    Office 365, PC
    Posts
    29

    Calculating weighted average by category in Power Query (or Power Pivot)

    I'm brand new to Power Query/DAX/BI. Been learning as I go without too many problems but this one has me stumped. I've tried a dozen different things but just can't get it right and would greatly appreciate your advice.

    I'm consolidating multiple tables using Power Query, and I want to add a calculated column with a weighted average summed by category. Some of the rows have values and others don't - I only want to average actual entries. Here's what it would look like if I were to do it manually (I need the green column added to the query):

    Attachment 759533

    I know a dynamic array could accomplish the same thing, but I've tens of thousands of rows that have to be imported in a strictly-fixed format so I need the calculation to run at the time of import. I'd really prefer to do it directly in Power Query so it's a one-step process, but Power Pivot is an acceptable alternative.

    Attached is a sanitized workbook with my actual use-case, including the existing query. In case it matters or if there's a better approach to the problem, here are the details: I'm importing price data and quantities from a DB and need to extract an average price for each item so the total value of both goods on hand (from in the DB) and actual inventoried physical goods (imported from a second table) can go in a single pivot table. I can get the average price to pivot easily, but I can't then use that as a static multiplier to extract other values because of how pivots automatically use aggregates.

    Screenshot 2021-12-15 105401.png

    I'm working in Power Query for the first time so I'd greatly appreciate full instructions with the context/syntax intact so I know I'm putting everything in the right place.

    Thank you so much!
    Attached Files Attached Files
    Last edited by Rabk; 12-15-2021 at 01:07 PM.

  2. #2
    Registered User
    Join Date
    08-12-2007
    Location
    United Sates
    MS-Off Ver
    Office 365, PC
    Posts
    29

    Re: Calculating weighted average by category in Power Query (or Power Pivot)

    Partially Solved: Power Pivot > Measure > New Measure

    SUMX(TblConsolidated_List,TblConsolidated_List[UNITCOST]*TblConsolidated_List[QtyOnHand])/[Sum of QtyOnHand]

    This works fine for everything that has a value under UNITCOST. I'm still looking for a solution for those that don't

  3. #3
    Registered User
    Join Date
    08-12-2007
    Location
    United Sates
    MS-Off Ver
    Office 365, PC
    Posts
    29

    Re: Calculating weighted average by category in Power Query (or Power Pivot)

    My end solution was to add a column to the source data that replaces blank values for UNITCOST with the weighted average of those that have them. Not ideal, but it's what I got.

+ 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. [SOLVED] Power Query/Power Pivot Conditional Column creation & chart
    By mz_h in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-12-2020, 06:07 PM
  2. [SOLVED] 2 fields checker- Request for alternative option in Power Query or Power Pivot
    By Shareez Saleem in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-15-2020, 08:36 AM
  3. Power Query / Power pivot : M2M relationships problem
    By almourasel in forum Excel General
    Replies: 6
    Last Post: 03-26-2020, 05:33 PM
  4. [SOLVED] Power Pivot does not load new column added in Power query
    By ibuhary in forum Excel General
    Replies: 12
    Last Post: 02-19-2019, 03:53 AM
  5. Sub-Forum for Excel Power Tools (Power Query, Power Pivot & Power BI)
    By chullan88 in forum Suggestions for Improvement
    Replies: 10
    Last Post: 06-28-2018, 02:25 PM
  6. Replies: 15
    Last Post: 03-29-2018, 11:29 AM
  7. Power Pivot or just regular Excel weighted average formula
    By stevemkiidub in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-14-2015, 12:17 PM

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