Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
sathin2
New Member

Pivoting a pivot table in Power BI

I'm relatively new to Power BI. I'd like to plot the 'simple' average of "weekly average price weight by quantity" by week. The two steps in the example below are:

1. For each row the price is 'weighted' by the quantity and averaged for each (person, week)

2. These numbers are 'simple' averaged across each person to obtain the weekly average

 

The raw data range is A1:E5. The desired output range is G10:H12. In Excel this is equivalent to pivoting on the pivot table, which I performed manually as shown below:

sathin2_2-1724951786890.png

 

Is there a way to preprocess the data to obtain this result in Power BI?

 

Note: In addition to the above there are row-wise filters to be applied. The raw data, the pivot and the final weekly average price per unit should update based on the selected filters.

1 ACCEPTED SOLUTION
ahadkarimi
Solution Specialist
Solution Specialist

Hi @sathin2, try these measures below, and if you encounter any issues, let me know.

 

Create a calculated column:

WeightedPrice = [Price] * [Quantity]

Create a measure:

WeeklyAvgPricePerPerson = 
DIVIDE(
    SUM('Table'[WeightedPrice]),
    SUM('Table'[Quantity])
)

Create another measure:

SimpleWeeklyAvg = 
AVERAGEX(
    VALUES('Table'[Person]),
    [WeeklyAvgPricePerPerson]
)

 

Did I answer your question? If so, please mark my post as the solution! ✔️
Your Kudos are much appreciated! Proud to be a Solution Supplier!

View solution in original post

2 REPLIES 2
ahadkarimi
Solution Specialist
Solution Specialist

Hi @sathin2, try these measures below, and if you encounter any issues, let me know.

 

Create a calculated column:

WeightedPrice = [Price] * [Quantity]

Create a measure:

WeeklyAvgPricePerPerson = 
DIVIDE(
    SUM('Table'[WeightedPrice]),
    SUM('Table'[Quantity])
)

Create another measure:

SimpleWeeklyAvg = 
AVERAGEX(
    VALUES('Table'[Person]),
    [WeeklyAvgPricePerPerson]
)

 

Did I answer your question? If so, please mark my post as the solution! ✔️
Your Kudos are much appreciated! Proud to be a Solution Supplier!

Figured out a solution:

SimpleWeeklyAvg1 =
AVERAGEX(
    SUMMARIZE('Table', [Person], [Week], "WeeklyAvgFrequencyPerPerson1", 'Table'[WeeklyAvgPricePerPerson]),
    [WeeklyAvgFrequencyPerPerson1]
)

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.