Forum Discussion
Solved
Pivot table with actuals for each month but average as an aggregation?
Hi, I have some data that shows expenses by date (month/quarter/year) and I'd like to create a pivot table so that I can see aggregated data by category for each month of the year. That is straig...
We could modify measure as
Expense Amount 01:=IF ( HASONEVALUE ( Range[Date (Quarter)] ), CALCULATE ( SUM ( Range[Amount] ), Range[Type] = "Expense" ), CALCULATE ( AVERAGE ( Range[Amount] ), Range[Type] = "Expense" ) )Result will be like
You'll need a separate pivot table with its own pivot cache (see Stop all the pivot tables applying the same grouping method)
See the attached version.
Thanks! This gives me a way to split the aggregations as well as see the data by different timeframes. However, my overall question was more pointed: can I have the details be "sum" but the grand total be "average" so that as I filter the different fields, I continue to see the average "for that view" instead of seeing the grand total for that view?