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?
That could be DAX measure like
Expense Amount := IF ( ISFILTERED ( Range[Date (Quarter)] ), CALCULATE ( SUM ( Range[Amount] ), Range[Type] = "Expense" ), CALCULATE ( AVERAGE ( Range[Amount] ), Range[Type] = "Expense" ) )which gives
- Thanks, this is great. It would work best when the timeframes are known (like Q1, Q2, etc.) but what I was hoping to do was to see monthly expenses and an average for the periods visible in the column.
Or in this case, let's say you filter out Qtr1, then I would like to see the sum for Qtr2, Qtr3 and Qtr4 and an average for the 3 quarters visible.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
No, that is not possible, as far as I know.