Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 those have to match.
Thing is : I have 1 row for Turnover, one row for Volume, and multiple other KPIs as well. In fact, I have a "KPI" Column containing the KPI i.e. Turnover, Volume and others, and a "Value" column with the numerical value.
I'd like to keep this structure but add a "Mean Price" KPI and the calculated value on new rows, keeping the other column values such as Store, regions, SKU etc. The data file is huge already, and has a lot of other columns, here's a sample.
The data :
SKU | Supplier | Store | Période | KPI | Value |
7,613E+12 | Brand1 | 9718 | P6 2023 | Volume | 37 |
7,613E+12 | Brand1 | 9711 | P6 2023 | Volume | 30 |
7,613E+12 | Brand1 | 9389 | P6 2023 | Volume | 59 |
7,613E+12 | Brand1 | 9322 | P6 2023 | Volume | 72 |
7,613E+12 | Brand1 | 9718 | P6 2023 | Turnover | 138 |
7,613E+12 | Brand1 | 9711 | P6 2023 | Turnover | 107 |
7,613E+12 | Brand1 | 9389 | P6 2023 | Turnover | 149 |
7,613E+12 | Brand1 | 9322 | P6 2023 | Turnover | 152 |
The desired outcome :
SKU | Supplier | Store | Période | KPI | Value |
7,613E+12 | Brand1 | 9718 | P6 2023 | Volume | 37 |
7,613E+12 | Brand1 | 9711 | P6 2023 | Volume | 30 |
7,613E+12 | Brand1 | 9389 | P6 2023 | Volume | 59 |
7,613E+12 | Brand1 | 9322 | P6 2023 | Volume | 72 |
7,613E+12 | Brand1 | 9718 | P6 2023 | Turnover | 138 |
7,613E+12 | Brand1 | 9711 | P6 2023 | Turnover | 107 |
7,613E+12 | Brand1 | 9389 | P6 2023 | Turnover | 149 |
7,613E+12 | Brand1 | 9322 | P6 2023 | Turnover | 152 |
7,613E+12 | Brand1 | 9718 | P6 2023 | MeanPrice | 3,73 |
7,613E+12 | Brand1 | 9711 | P6 2023 | MeanPrice | 3,57 |
7,613E+12 | Brand1 | 9389 | P6 2023 | MeanPrice | 2,53 |
7,613E+12 | Brand1 | 9322 | P6 2023 | MeanPrice | 2,11 |
I need MeanPrice to be new KPI row so I favour PowerQuery instead of a DAX measure. Can you help me for the formula ? Thanks in advance !
I 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.
Proud to be a Super User! | |
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
65 | |
64 | |
56 | |
39 | |
27 |
User | Count |
---|---|
85 | |
59 | |
45 | |
43 | |
38 |