Forum Discussion
Pivot table, get Average by Total Numbers of months
Formula solution could work, but only if PivotTable is static, i.e. not sorted, not filtered and range is not updated.
Add both SUM() and AVERAGE() aggregations in Grand Total without having the same for each month. Some columns could be hided, but from my point of view that's not reliable solution.
I'd suggest to change layout a bit. First, creating PivotTable add data to data model, with that add measure as
Average Qty:=VAR monthsSelected= CALCULATE( DISTINCTCOUNT( Range[Purchasedate (Month)] ), ALLEXCEPT(Range, Range[Purchasedate (Month)] ) ) RETURN DIVIDE(SUM(Range[Quantity]), monthsSelected)For PivotTable move Values to Rows pane
with that layout will be as
That gives extra rows, perhaps that's better than extra columns.
As for number of months, we could take one for TODAY(), but with that we will have wrong average with filtering of months. For example, if we select only third quarter, we will have correct average with above formula
Please check the last sheet in attached
Riny_van_Eekelen please have a look at the attached file.
the problem is when i use average function it averages only counted number of month in which i made purchase.
But i want the average of total months that is 11 Months by now.
Formula solution could work, but only if PivotTable is static, i.e. not sorted, not filtered and range is not updated.
Add both SUM() and AVERAGE() aggregations in Grand Total without having the same for each month. Some columns could be hided, but from my point of view that's not reliable solution.
I'd suggest to change layout a bit. First, creating PivotTable add data to data model, with that add measure as
Average Qty:=VAR
monthsSelected=
CALCULATE(
DISTINCTCOUNT(
Range[Purchasedate (Month)] ),
ALLEXCEPT(Range, Range[Purchasedate (Month)]
)
)
RETURN DIVIDE(SUM(Range[Quantity]), monthsSelected)For PivotTable move Values to Rows pane
with that layout will be as
That gives extra rows, perhaps that's better than extra columns.
As for number of months, we could take one for TODAY(), but with that we will have wrong average with filtering of months. For example, if we select only third quarter, we will have correct average with above formula
Please check the last sheet in attached