SOLVED

Pivot table, get Average by Total Numbers of months

Copper Contributor
I have an excel data table which have these colomns, Purchae date, Item name, Quantity, Supplier name, Now i want to get average of quantity by total number of months. I.e quanty sum is 50 and this is tenth month. So the average should be 5 per month. More over this should keep updating as the month number go on. Best regards
4 Replies

@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.

@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.

@ndmalam Perhaps like in the attached? Otherwise I don't undertand what you ask for.

best response confirmed by ndmalam (Copper Contributor)
Solution

@ndmalam 

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

image.png

with that layout will be as

image.png

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

image.png

Please check the last sheet in attached

1 best response

Accepted Solutions
best response confirmed by ndmalam (Copper Contributor)
Solution

@ndmalam 

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

image.png

with that layout will be as

image.png

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

image.png

Please check the last sheet in attached

View solution in original post