Forum Discussion
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 straightforward.
However, I'd also like to see an average spend by category for a variable timeframe. For example, I'd like to see average food expenses for the first half of the year or average travel expenses in April/May/June. I tried making the amount summarized as average, but it also averages the values for each month which is not what I am looking for. I'd only like to see the average amount in the "grand total" column.
I am attaching a sample dataset along with the pivot table. Any ideas on how I can achieve it?
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
8 Replies
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
