Nov 05 2020 09:47 PM
Nov 05 2020 10:19 PM
@ndmalam I suppose you have the current date somewhere in your sheet. If not, enter it somewhere, for instance in A1:
=TODAY()
Then, in another cell (say A2) enter:
=MONTH(A1)
This will give you the current number of the month.
Then you can take the sum of quantities and divide it be the number in A2
=<total qty>/A2
where <total qty> refers to a SUM range for the total quantity or a cell where the total quantity already resides.
I have attached a very basis example, using a structured table in the attached file.
Nov 06 2020 07:56 AM
@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.
Nov 06 2020 10:57 AM
@ndmalam Perhaps like in the attached? Otherwise I don't undertand what you ask for.
Nov 06 2020 12:51 PM
SolutionFormula 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
Nov 06 2020 12:51 PM
SolutionFormula 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