Dec 22 2021 10:45 AM
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?
Dec 22 2021 01:29 PM
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.
Dec 22 2021 01:55 PM
Dec 22 2021 02:03 PM
No, that is not possible, as far as I know.
Dec 23 2021 06:03 AM
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
Dec 23 2021 10:55 AM
Dec 24 2021 04:15 AM
SolutionWe 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
Dec 24 2021 11:53 AM
Dec 25 2021 10:55 AM
@Romit Mehta , you are welcome, glad it helped
Dec 24 2021 04:15 AM
SolutionWe 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