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!
Bookmarks