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