cancel
Showing results for
Did you mean:
Responsive Resident

## How to create a measure to calculate Weighted Averages and Standard Deviations from unpivoted data

I'm trying to create two Matrices, one with weighted average Price, and the other with weighted average Percentage Change.  I'd also like to measure the standard deviation of Percentage Change for a given Model Year of a vehicle.

I have data in Excel that I unpivot in PowerQuery and then pull into BI.  The "before unpivot" data that gets unpivoted consists of several columns of data, which are essentially:

0% Used in Current Transaction Year

0% Used in Prior Transaction Year

Annualized % Change of 0% Used

and then repeated for each of 20%, 40%, 50%, 60%, 80%, and 100%.  Data that remains pivoted include [Model Year Units] (i.e., number of units built in a model year).

Here's an example of what the data looks like before unpivoting:

 Model Type Model Year Units Model Year Transaction Year 0% Used in Current Transaction Year 0% Used in Prior Transaction Year Annualized % Change of 0% Used A 20 2005 2010 \$10,000 A 20 2005 2011 \$9,000 \$10,000 -10.00% A 20 2005 2012 \$8,000 \$9,000 -11.11% B 15 2005 2010 \$5,000 B 15 2005 2011 \$4,000 \$5,000 -20.00% B 15 2005 2012 \$3,000 \$4,000 -25.00%

I can get a Matrix of Average values as shown below but this doesn't weigh by [Model Year Units], it's simply the average of all instances (Average summary in a Pivot Table).

This output below is from an Excel file, but is similar to the output I'd like for a Power BI Matrix when sliced or unsliced by Model.  The columns to the left in the table below are the simple average and standard deviations of the rows associated directly to their right.

I'm stuck at this point and don't know how to create the measures I need to generate the desired output.

*************************

**Update**

With various iterations of the following formula, I'm able to (generally) create the targeted Matrix data:

``````zzAverage Annualized Price change (weighted by Model Year Units) =
VAR DESIRED_ROWS =
FILTER(
'Helivalues Transaction History',
'Helivalues Transaction History'[Model Year Units] > 0      &&
'Helivalues Transaction History'[Attribute] = "0% annual % change"  ||
'Helivalues Transaction History'[Attribute] = "10% annual % change" ||
'Helivalues Transaction History'[Attribute] = "20% annual % change" ||
'Helivalues Transaction History'[Attribute] = "40% annual % change" ||
'Helivalues Transaction History'[Attribute] = "50% annual % change" ||
'Helivalues Transaction History'[Attribute] = "60% annual % change" ||
'Helivalues Transaction History'[Attribute] = "80% annual % change" ||
'Helivalues Transaction History'[Attribute] = "100% annual % change"
)

RETURN

DIVIDE(
SUMX (
DESIRED_ROWS,
'Helivalues Transaction History'[Model Year Units] * 'Helivalues Transaction History'[Value]
),
SUMX (
DESIRED_ROWS,
'Helivalues Transaction History'[Model Year Units]
)
)``````

but this creates problems with a slicer when I want to be able to choose between, e.g., 0% Used, because the above code filters out that row.  I still don't know how to go from the above Measure to get the annual average and standard deviations.

1 ACCEPTED SOLUTION
Responsive Resident

The format for the programming of to find the Average and StandardDeviation by rows has now been done.

For Average:

``````Row Average =
// // In the "SUMMARIZE" section below, the first is the Table, second is the column from that table that represents the Matrix **COLUMNS**, and third is the column of data from that table that represents the Matrix **ROWS**.
// After SUMMARIZE is closed, the next item is the MEASURE of the target criteria that one is looking to report the **average** of by Matrix rows.

RETURN

AVERAGEX (
SUMMARIZE (
'Helivalues Transaction History',
'Helivalues Transaction History'[Sale Year],
'Helivalues Transaction History'[Year of Build]
),
[zzAverage Annualized Price change (weighted by Model Year Units)]
)
)``````

For Standard Deviation:

``````Row Average =
// In the "SUMMARIZE" section below, the first is the Table, second is the column from that table that represents the Matrix **COLUMNS**, and third is the column of data from that table that represents the Matrix **ROWS**.
// After SUMMARIZE is closed, the next item is the MEASURE of the target criteria that one is looking to report the **standard deviation** of by Matrix rows.

RETURN

STDEVX.S (
SUMMARIZE (
'Helivalues Transaction History',
'Helivalues Transaction History'[Sale Year],
'Helivalues Transaction History'[Year of Build]
),
[zzAverage Annualized Price change (weighted by Model Year Units)]
)
)``````
Responsive Resident

The format for the programming of to find the Average and StandardDeviation by rows has now been done.

For Average:

``````Row Average =
// // In the "SUMMARIZE" section below, the first is the Table, second is the column from that table that represents the Matrix **COLUMNS**, and third is the column of data from that table that represents the Matrix **ROWS**.
// After SUMMARIZE is closed, the next item is the MEASURE of the target criteria that one is looking to report the **average** of by Matrix rows.

RETURN

AVERAGEX (
SUMMARIZE (
'Helivalues Transaction History',
'Helivalues Transaction History'[Sale Year],
'Helivalues Transaction History'[Year of Build]
),
[zzAverage Annualized Price change (weighted by Model Year Units)]
)
)``````

For Standard Deviation:

``````Row Average =
// In the "SUMMARIZE" section below, the first is the Table, second is the column from that table that represents the Matrix **COLUMNS**, and third is the column of data from that table that represents the Matrix **ROWS**.
// After SUMMARIZE is closed, the next item is the MEASURE of the target criteria that one is looking to report the **standard deviation** of by Matrix rows.

RETURN

STDEVX.S (
SUMMARIZE (
'Helivalues Transaction History',
'Helivalues Transaction History'[Sale Year],
'Helivalues Transaction History'[Year of Build]
),
[zzAverage Annualized Price change (weighted by Model Year Units)]
)
)``````

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

#### Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors