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 ...
Need Help
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