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

[mrothschild]

Link to PBIX file is: https://drive.google.com/file/d/1j_4CrUj0fPh0xK6hnO7OZ-uIWkoP5Kdg/view?usp=sharing

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.  

Any help, please?  Thanks!

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

**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.

[mrothschild · Accepted solution]

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)]
            ) 
        )