cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
Remi_B_Q
New Member

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 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 :

SKUSupplierStorePériodeKPIValue
7,613E+12Brand19718P6 2023Volume37
7,613E+12Brand19711P6 2023Volume30
7,613E+12Brand19389P6 2023Volume59
7,613E+12Brand19322P6 2023Volume72
7,613E+12Brand19718P6 2023Turnover138
7,613E+12Brand19711P6 2023Turnover107
7,613E+12Brand19389P6 2023Turnover149
7,613E+12Brand19322P6 2023Turnover152

 

The desired outcome :

SKUSupplierStorePériodeKPIValue
7,613E+12Brand19718P6 2023Volume37
7,613E+12Brand19711P6 2023Volume30
7,613E+12Brand19389P6 2023Volume59
7,613E+12Brand19322P6 2023Volume72
7,613E+12Brand19718P6 2023Turnover138
7,613E+12Brand19711P6 2023Turnover107
7,613E+12Brand19389P6 2023Turnover149
7,613E+12Brand19322P6 2023Turnover152
7,613E+12Brand19718P6 2023MeanPrice3,73
7,613E+12Brand19711P6 2023MeanPrice3,57
7,613E+12Brand19389P6 2023MeanPrice2,53
7,613E+12Brand19322P6 2023MeanPrice2,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 !

1 REPLY 1
audreygerred
Impactful Individual
Impactful Individual

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. 

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors