Deprecated: Creation of dynamic property ternplugin\youtube_video::$post is deprecated in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/automatic-video-posts/vendor/ternstyle/plugin/src/class/youtube_video.php on line 64

Excel Average Of Non-Zero Rows In Pivot Table – 2358

Posted on by Annie D

There are 7 pivot table secrets in this video, and you haven’t seen six of them. The question: I have several projects in a pivot table. At the bottom, as part of the pivot table, I want an average of the non-zero rows.

The person is currently using AVERAGEIF outside of the pivot table, but that formula must be adjusted.

Today, we use a Data Model pivot table and a DAX Measure to calculate the average. The resulting pivot table has too many rows, so I use a new Set based on Rows.

I can not post the final formula here because YouTube won’t allow greater than or less than in the description.

The 8 secrets:
Secret 1: Unlock features with this checkbox
Secret 2: Adding a calculated field using DAX
Secret 3: DIVIDE function
Secret 4: Calculation based on numbers in pivot
Secret 5: CALCULATE function in DAX
Secret 6: DISTINCTCOUNT function in DAX
Secret 7: Create a Set based on Rows in pivot
Secret 8: Filtering a measure created on the fly

Table of Contents
(0:00) 7 Pivot Table Secrets
(0:12) Description of Problem
(0:48) AVERAGEIF function in Excel
(1:14) What is DAX
(1:31) How to unlock DAX
(1:53) Rename the Table
(2:08) Add this data to the data model
(2:33) Implicit measures do work
(2:55) Include Filtered Items in Totals
(3:03) Creating a DAX Calculation
(3:49) DIVIDE function in DAX
(4:00) Using a summary cell from pivot table in new calculation
(4:15) CALCULATE function in DAX
(4:25) DISTINCTCOUNT function in DAX
(5:57) Discussion about denominator from detail rows
(6:04) Moving VALUES tile in the pivot table
(6:44) Excel Number Format for Thousands
(7:38) How to hide unwanted rows in pivot table
(7:57) Create a Set based on Row items
(8:54) Creating a Set clears number formatting
(9:26) Learn DAX from Excel Off the Grid
(10:03) Adding new data & refreshing
(10:39) Refresh the pivot table
(11:36) Is adding rows to the set a deal breaker?
(12:03) Using Summary as Denominator in DAX
(12:24) Replacing implicit measure in DAX
(12:42) Building a Table on the fly then filtering it
(12:50) Counting the Rows in the table
(13:09) Summary

Further Help

I offer limited consulting services to potentially assist you with data challenges, whether it's designing a complex Excel formula, writing a macro or building a whole new process for data capture, modeling and analysis.  Contact me if you have a need.

MrExcel.comFri, January 20, 2023 5:37pm