Pivot table with actuals for each month but average as an aggregation?

[Romit Mehta]

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? 

[#2]

@Romit Mehta 

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.

[#3]

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?

[#4]

@Romit Mehta 

No, that is not possible, as far as I know.

[#5]

@Romit Mehta 

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

[#6]

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.

[#7 · Accepted solution]

@Romit Mehta 

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]

Perfect. Thank you so much. I was able to change the timeframe to just months and it all works.

[#9]

@Romit Mehta , you are welcome, glad it helped