Matrix - row sub-totals for weighted average (SUMPRODUCT) [measures], not simple avg?

[mrothschild]

________________________________

PBIX file: https://drive.google.com/file/d/12_k_tO8BdrXV73Lbho_whabGP-jOlasP/view?usp=sharing

Excel sample data exported from above: https://docs.google.com/spreadsheets/d/12nChVgYinw5RMB-M5S4lv96hLQQzMwjT/edit?usp=sharing&ouid=11511...

On the tab, "Troubleshooting for forum help", I'm trying to summarize the columnar averages of the Matrix that looks like the following:

The individual cell data is all correct.  The [measures] for columns circled in red (except for {B}) were calculated with coding meant to replicate Excel's SUMPRODUCT function.  For example the coding for {A} is:

{A}  = 

VAR OUTPUT =
        
            STDEVX.S(
                SUMMARIZE (
                        ALLSELECTED('Helivalues Transaction History'),
                        'Helivalues Transaction History'[Sale Year]

                    )
                , [Average Annualized Price change (weighted by Model Year Units)]
            )
        
RETURN
    OUTPUT

and the coding for [Average Annualized Price change (weighted by Model Year Units)] is:

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

I followed the recommendations from @Greg_Deckler's helpful Forum post here: https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

While the advice provided in that post works for simple averages, as indicated by the Matrix columns of "Avg Units" and "_TEST {A Avg}, I can't seem to get it to work for my weighted average [measures] of {A}, {C} and {D}

Thanks for any/all help!

[Greg_Deckler]

@mrothschild I knew when I posted that it was a tad arrogant to assume that it was the final word on the subject. Should have put final in double quotes. 🙂

[mrothschild]

@Greg_Deckler 

A little progress here, and I'm guessing you'll be able to sort the last piece quickly.  

PBIX: https://drive.google.com/file/d/12_k_tO8BdrXV73Lbho_whabGP-jOlasP/view?usp=sharing

This screenshot of the Matrix isn't sliced on "Year of Build" and is generating the row sub-total for the measure {_TEST A} correctly - hooray!

But as soon as I slice on "Year of Build", even if the slicer shouldn't be impacting the Matrix because the range remains below where this particular would be affected, I get NaN errors:

The coding for _TEST {A} is: 

_TEST - {A} = 

VAR OUTPUT_CELL = 
    AVERAGEX(
       SUMMARIZE('Helivalues Transaction History','Helivalues Transaction History'[Year of Build])
        , [______A - Depreciation Volatility Observed]
    )
RETURN
    OUTPUT_CELL

and I'm guessing/hoping it has to do with the way the AVERAGEX is being summarized??

[mrothschild · Accepted solution]

Final PBIX File here: https://drive.google.com/file/d/12_k_tO8BdrXV73Lbho_whabGP-jOlasP/view?usp=sharing

Code to create the weighted average measure:

WAVG Measure = 

VAR DESIRED_ROWS = 
    FILTER(
        'Table',
        'Table'[Column1] > 0      &&
        'Table'[Attribute (from a PowerQuery unpivot] = "attribute1 selected"  ||
        'Table'[Attribute (from a PowerQuery unpivot] = "attribute2 selected"  ||
        'Table'[Attribute (from a PowerQuery unpivot] = "attribute3 selected"  ||
    )

RETURN
    DIVIDE(
            SUMX (
                DESIRED_ROWS,
                'Table'[Denominator Column] * 'Table'[Value]
            ),
            SUMX (
                DESIRED_ROWS,
                'Table'[Denominator Column]
            )
    )

Code to generate Matrix values from a weighted average measure:

_A = 

// Note - below one can use any of the *X DAX functions, such as SUMX, AVERAGEX, MEDIANX, STDEVX.S, etc.
       
VAR OUTPUT =     
            AVERAGEX(
                SUMMARIZE (
                        ALLSELECTED('Table'),
                        'Table'[Row Column]
                    )
                , [WAVG Measure]
            )
        
RETURN
    OUTPUT
       

Code to summarize the Matrix value [measure]

_A Summarized = 

// Choose AVERAGEX or SUMX below depending on one's needs


VAR OUTPUT_CELL = 
    AVERAGEX(
      SUMMARIZE('Table','Table'[Matrix Row Column])
        , [WAVG measure]
    )
RETURN
    OUTPUT_CELL

[mrothschild]

@Greg_Deckler 

Now you have the chance!!  😉

PBIX File here: https://drive.google.com/file/d/12_k_tO8BdrXV73Lbho_whabGP-jOlasP/view?usp=sharing

On tab, "Duplicate of Troubleshooting. . ." is the following Matrix

I'm trying to get a Unit-weighted summary average of {A} in the above Matrix

{A int1} is the product of [Units avg] and {A} and is calculating correctly.  If I can get {A int1} summarizing correctly, then I believe  I can take care of the rest and clean everything up.  As shown on the screenshot, the summarized row sub-totals of {A Int1} seemingly bear no relation to its associated cells.