Forum Discussion

rahulvadhvania's avatar
rahulvadhvania
Copper Contributor
Apr 08, 2021
Solved

How to let Pivot Table ignore ZEROs from the data while calculating Average and Average of Average?

Hello,   Thank you for your time to review my post.   I have a huge database being supported by pivot tables and pivot charts. The field value used is Average. I am trying to find a way that pivo...
excel
willi1950's avatar
willi1950
Copper Contributor
May 14, 2023

SergeiBaklan i have a similar issue but my data is grouped ( by 5 days ), so if i add the data to a data model, although it lets me set up a measure as per your previous  reply. This disables the grouping.

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    May 22, 2023

    willi1950 

    With data model grouping is based on calculated columns. If to select, for example, group by month, Power Pivot automatically creates calculated column as

    =FORMAT([Date], "MMM")

    For grouping by dates parameters are disabled for such case, as for any other non-standard grouping. However, we may create such calculated column ourselves, like

    =
VAR startDate =
    DATE ( 2022, 5, 6 )
VAR step = 5
VAR isInPeriod =
    MOD ( Table1[Date] - startDate, step )
VAR periodNo =
    INT ( ( Table1[Date] - startDate ) / step )
VAR startPeriod = startDate + step * periodNo
VAR endPeriod = startDate + step * ( periodNo + 1 ) - 1
RETURN
    FORMAT ( startPeriod, "yyyy-mm-dd" ) & " - "
        & FORMAT ( endPeriod, "yyyy-mm-dd" )

    and use it in PivotTable.

Resources