topic Re: Pivoting a pivot table in Power BI in DAX Commands and Tips

Pivoting a pivot table in Power BI

sathin2 — Thu, 29 Aug 2024 17:33:56 GMT

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.

Re: Pivoting a pivot table in Power BI

ahadkarimi — Thu, 29 Aug 2024 17:44:17 GMT

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!

Re: Pivoting a pivot table in Power BI

sathin2 — Thu, 29 Aug 2024 19:30:29 GMT

Figured out a solution:

SimpleWeeklyAvg1 =

AVERAGEX(

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

[WeeklyAvgFrequencyPerPerson1]

)