cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mrothschild
Responsive Resident
Responsive Resident

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

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 TypeModel Year UnitsModel YearTransaction Year0% Used in Current Transaction Year0% Used in Prior Transaction YearAnnualized % Change of 0% Used
A2020052010$10,000  
A2020052011$9,000$10,000-10.00%
A2020052012$8,000$9,000-11.11%
B1520052010$5,000  
B1520052011$4,000$5,000-20.00%
B1520052012$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).

 

mrothschild_0-1627842251159.png

 

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.  

 

mrothschild_1-1627843159102.png

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.

1 ACCEPTED SOLUTION
mrothschild
Responsive Resident
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)]
            ) 
        )

View solution in original post

1 REPLY 1
mrothschild
Responsive Resident
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)]
            ) 
        )

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

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.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

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

R2 (Green) 768 x 460px.png

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