Forum Discussion
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...
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 ) ) )
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.
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!
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 ) ) )- Thank you so much! This is exactly what I needed!
You are welcome, glad to help
If you post a very small, copiable dataset together with expected results I can show you the necessary measures.
Regards- The code Sergei posted solved my problem already, but thank you very much for your offer!
