Data Model Pivot Table Average Subtotals

New Contributor

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!

7 Replies


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.

@Sergei Baklan 
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?



If you post a very small, copiable dataset together with expected results I can show you the necessary measures.


best response confirmed by RodoFliess (New Contributor)


For such sample


measure could be

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


Thank you so much! This is exactly what I needed!
The code Sergei posted solved my problem already, but thank you very much for your offer!


You are welcome, glad to help