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

     

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

 

  • RodoFliess's avatar
    RodoFliess
    Copper Contributor
    Feb 22, 2023
    Thank you so much! This is exactly what I needed!

Resources