Forum Discussion

mrothschild's avatar
mrothschild
Continued Contributor
5 years ago

Measure to average a pivot-table column in a Matrix with errors treated as blanks?

 

PBIX table is here: https://drive.google.com/file/d/1pFgmqsQ0m8YYPZSIPIhy8jy98FFWXe3l/view?usp=sharing

 

[Measure] formula for the values in the pivot table:

 

______A - workaround until you figure out the FiNAL PIECE - Depreciation Volatility Observed = 
//DEPRECIATION VOLATILITY OBSERVED
       
VAR DEPRECIATION_VOL_OBSERVED =

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

RETURN
        IF(
            HASONEVALUE('Helivalues Transaction History'[Year of Build])
            , DEPRECIATION_VOL_OBSERVED
            , AVERAGEX('Helivalues Transaction History', DEPRECIATION_VOL_OBSERVED)
        )

 

 

When this is sub-totaled on rows it results in NaN errors.  As a result, when I've tried the following formula for a [measure] to produce the average of each pivot table-column of data, this formulation of the [measure] returns NaN.

 

 

______A01 - Average of  {A} in a given {year of build, ie by row} = 

VAR OUTPUT = 
        AVERAGEX( 
            ADDCOLUMNS (
                SUMMARIZE (
                    ALLSELECTED ('Helivalues Transaction History'[Sale Year]),
                    'Helivalues Transaction History'[Sale Year],
                    "SUM", SUM ( 'Helivalues Transaction History'[Year of Build] )
                ),
                "Percent", CALCULATE ( SUM ( 'Helivalues Transaction History'[Year of Build] ) )  
            ),
            [______A - workaround until you figure out the FiNAL PIECE - Depreciation Volatility Observed]
)

RETURN
    OUTPUT

 

 

Below is a screenshot of a Matrix showing each of the above [measures].  

 

The intended output is for the {A01} pivot tabled-column to produce the average of the pivot table column immediate to its left.  Based on what's shown {A01} should = 5.38% for 2016, 5.73% for 2017, 11.13% for 2018, 6.14% for 2019, and 6.14% for 2020.

 

I've attempted to summarize the first measure by trying to filter on only non-error rows, but haven't been able to do that and the filter to summarize by [Sale Year] at the same time.  Also not sure this will work, but was something that I thought might, but haven't been able to figure out how to test it.

 

Thanks!

Need Help

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

    mrothschild I think you want:

     

    VAR OUTPUT = 
        AVERAGEX( 
            ADDCOLUMNS (
                SUMMARIZE (
                    ALLSELECTED ('Helivalues Transaction History'[Sale Year]),
                    'Helivalues Transaction History'[Sale Year],
                    "SUM", SUM ( 'Helivalues Transaction History'[Year of Build] )
                ),
                "__Value", [______A - workaround until you figure out the FiNAL PIECE - Depreciation Volatility Observed] )  
            ),
            [__Value]
)

RETURN
    OUTPUT

    This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

    Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
    https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

     

    • mrothschild's avatar
      mrothschild
      Continued Contributor
      5 years ago

      Greg_Deckler 

       

      I've tried both the way you suggested and my way, and the Matrix output is identical under both versions.  

       

      VAR OUTPUT_NEW = 


        AVERAGEX( 
            ADDCOLUMNS (
                SUMMARIZE (
                    ALLSELECTED ('Helivalues Transaction History'[Sale Year]),
                    'Helivalues Transaction History'[Sale Year],
                    "SUM", SUM ( 'Helivalues Transaction History'[Year of Build] )
                ),
                "__Value", [______A - workaround until you figure out the FiNAL PIECE - Depreciation Volatility Observed]
            )  ,
            [__Value]
        )

VAR OUTPUT_OLD = 
 
        AVERAGEX( 
            ADDCOLUMNS (
                SUMMARIZE (
                    ALLSELECTED ('Helivalues Transaction History'[Sale Year]),
                    'Helivalues Transaction History'[Sale Year],
                    "SUM", SUM ( 'Helivalues Transaction History'[Year of Build] )
                ),
                "Percent", CALCULATE ( SUM ( 'Helivalues Transaction History'[Year of Build] ) )  
            ),
            [______A - workaround until you figure out the FiNAL PIECE - Depreciation Volatility Observed]
        )

RETURN
    • mrothschild's avatar
      mrothschild
      Continued Contributor
      5 years ago

      Greg_Deckler 

       

      Some progress here.  New PBIX file: https://drive.google.com/file/d/1pG3ua4BOWyIJhhh4dOwG9ac8UPWqUsYo/view?usp=sharing

       

      By changing the first [measure] to the following by adding ALLNOBLANKROW first thing after summarize

      VAR DEPRECIATION_VOL_OBSERVED =
        
            STDEVX.S(
                SUMMARIZE (
                        ALLNOBLANKROW('Helivalues Transaction History'),
                        'Helivalues Transaction History'[Sale Year]
                    )
                , [Average Annualized Price change (weighted by Model Year Units)]
            )

       

      I get **a** sub-total rather than NaN.  What that subtotal represents isn't entirely apparent, and isn't dynamic across the pivoted columns.  It's summarized as 5.76% for each column and the grand total, rather than the average of what I perceive to be the available data points.

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi mrothschild 

        Your sample is complex and there may be some sensitive data in your pbix file, please remove the link and share a easy sample with us. You can show us the result you want by screenshot as well. This may make it easier for us to understand your requirements.

         

        Best Regards,
        Rico Zhou

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.