Pivoting a pivot table in Power BI

[sathin2]

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:

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.

[ahadkarimi · Accepted solution]

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!

[sathin2]

Figured out a solution:

SimpleWeeklyAvg1 =

AVERAGEX(

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

    [WeeklyAvgFrequencyPerPerson1]

)