Forum Discussion

RodoFliess's avatar
RodoFliess
Copper Contributor
Feb 17, 2023
Solved

Data Model Pivot Table Average Subtotals

Hello Everyone, I´m having an issue witha a pivot table from the data model.

I want the quarterly subtotals to show monthly average rather than sum, but the option is grayed out:

Is there a way i can work around this?

Thank you!

excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Feb 18, 2023

    RodoFliess 

    For such sample

    measure could be

    Total:=IF (
    HASONEVALUE ( tbl[Date (Month)] ),
    SUM ( tbl[Value] ),
    AVERAGEX (
        VALUES ( tbl[Date (Month)] ),
        DIVIDE ( SUM ( tbl[Value] ), COUNTROWS ( VALUES ( tbl[Date (Month)] ) ), 0 )
    )
)

     

7 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Feb 17, 2023

    RodoFliess 

    If you are on data model I'd recommend not to use implicit measures at all. Create your own DAX explicit measures with SUM, AVERAGE, whatever and use them in PivotTable.

    • RodoFliess's avatar
      RodoFliess
      Copper Contributor
      Feb 17, 2023

      SergeiBaklan 
      Thanks for your reply!
      I´m not very good with DAX measures, how could I create a measure that sums by month and then shows quarter monthly average?

       

      Thanks!

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Feb 18, 2023

        RodoFliess 

        For such sample

        measure could be

        Total:=IF (
    HASONEVALUE ( tbl[Date (Month)] ),
    SUM ( tbl[Value] ),
    AVERAGEX (
        VALUES ( tbl[Date (Month)] ),
        DIVIDE ( SUM ( tbl[Value] ), COUNTROWS ( VALUES ( tbl[Date (Month)] ) ), 0 )
    )
)

         

Resources