Forum Discussion

ndmalam's avatar
ndmalam
Copper Contributor
Nov 06, 2020
Solved

Pivot table, get Average by Total Numbers of months

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
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Nov 06, 2020

    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

    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

4 Replies

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Nov 06, 2020

    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.

    • ndmalam's avatar
      ndmalam
      Copper Contributor
      Nov 06, 2020

      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.

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Nov 06, 2020

        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

        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

Resources