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
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.
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
- Perfect. Thank you so much. I was able to change the timeframe to just months and it all works.
Romit Mehta , you are welcome, glad it helped