Forum Discussion

Romit Mehta's avatar
Romit Mehta
Brass Contributor
Dec 22, 2021
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...
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Dec 24, 2021

    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

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Dec 24, 2021

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

Romit Mehta's avatar
Romit Mehta
Brass Contributor
Dec 24, 2021
Perfect. Thank you so much. I was able to change the timeframe to just months and it all works.