SOLVED

Data Model Pivot Table Average Subtotals

Copper 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:

RodoFliess_0-1676642296536.pngRodoFliess_1-1676642332448.png

Is there a way i can work around this?

Thank you!

7 Replies

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

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

 

Thanks!

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


Regards

best response confirmed by RodoFliess (Copper Contributor)
Solution

@RodoFliess 

For such sample

image.png

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

 

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!

@RodoFliess 

You are welcome, glad to help

1 best response

Accepted Solutions
best response confirmed by RodoFliess (Copper Contributor)
Solution

@RodoFliess 

For such sample

image.png

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

 

View solution in original post