Forum Discussion
Dividing from different rows into another row in Power Query
Hi ! I have to add a "Mean Price" row to a table, based on a simple calculation (Turnover / Volume sold). I have turnover and Quantities sold for many stores, and for each product reference, so thos...
audreygerred
Super User
Super UserI highly suggest utilizing DAX measures for aggregation rather than attempting to make it work in Power Query. For this example, you could do the following:
Volume = CALCULATE(SUM('Fact'[Value]), 'Fact' [KPI] IN {"Volume"})
Turnover = CALCULATE(SUM('Fact'[Value]), 'Fact' [KPI] IN {"Turnover"})
And make other explicit measures based on what is in your KPI field, then utilize those measures to create a measure for the Mean Price.
Alternatively, you can pivot the KPI column in PowerQuery, then do SUM measures (or whatever aggregation you need) on each of the columns with their value.