Forum Discussion

Frutank's avatar
Frutank
Copper Contributor
Mar 10, 2021

Using average values, how to change 0 in grand total in a pivot table into real numbers.

I have a lot of data using AVERAGE values in the column of the Pivot Table. But, there are some of my data that contains 0 and it makes the grand total is 0. All I want to do is changing the way that the grand total is calculated. I don't want Grand Total to include 0 and instead count the real numbers. What step can I do to accomplish this?

Thank you! I appreciate any help. Hope that makes sense. Let me know if you are still confused.

3 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor

    Frutank 

    Creating PivotTable table you may add data to data model and add DAX measure for such average like

    ASverage Without Zero:=CALCULATE(AVERAGEA(Table1[V]), Table1[V] <> 0)

     

Resources