Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculating Average Monthly Quantity

I've data that contains orders from customers along with quantities of each item.

 

I wish to calculate the monthly average of each material per customer. I tried to use the average function but the numbers didn't add up properly.

 

I'm trying to generate report similar to this one

 

Material#Material DescriptionCustomerAverage Monthly Quantity
123456PencilsStaples 
123456PencilsWalmart 
123456PencilsCostco 
654789SharpnerStaples 
654789SharpnerWalmart 
654789SharpnerCostco 
987456InkStaples 
987456InkWalmart 
987456InkCostco 

 

My Sample Data looks like this

 

DateMaterial#Material DescriptionCustomerQuantity
1/1/2021123456PencilsStaples100
1/1/2021654789SharpnerWalmart400
25/1/2021654123PaperCostco300
1/3/2021123456PencilsWalmart300
2/3/2021987456InkStaples500
15/3/2021654789SharpnerCostco600
2/4/2021123456PencilsStaples100
10/4/2021654123PaperWalmart400
15/4/2021987456InkStaples300
10/5/2021987456InkCostco200
15/5/2021123456PencilsCostco500
20/5/2021654789SharpnerStaples100
7/7/2021654123PaperCostco200
8/7/2021123456PencilsCostco500
20/7/2021987456InkWalmart400
2/8/2021654789SharpnerStaples300
24/08/2021123456PencilsWalmart100
15/9/2021987456InkCostco200
20/10/2021123456PencilsStaples200
25/10/2021654123PaperCostco300
29/10/2021123456PencilsStaples200
17/11/2021654789SharpnerWalmart600
20/11/2021987456InkWalmart200
27/11/2021654789SharpnerCostco400
10/12/2021123456PencilsWalmart400
15/12/2021654123PaperCostco300
16/12/2021123456PencilsWalmart200
Need Help
  • bcdobbs's avatar
    bcdobbs
    4 years ago

    Try the following:

    1) Create a date table:

     

     

    Date = 
    VAR EarliestYear = YEAR ( MIN ( Sales[Date] ) )
    VAR LatestYear = YEAR ( MAX ( Sales[Date] ) )

    RETURN
    ADDCOLUMNS (
        CALENDAR (
            DATE ( EarliestYear, 1, 1 ),
            DATE ( LatestYear, 31, 12 )
        ),
        "Month", DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 1 ) //Format as mmmm YYYY
    )

     

     

     

    2) Set column types as date for both and format Month as mmmm YYYY

    3) Mark it as a date table.

    4) Create relationship to your main table (I called mine Sales)

    5) Create a measure in the main table:

     

     

    Average Monthly Quantity = 
    AVERAGEX (
        VALUES ( 'Date'[Month] ),
        CALCULATE ( SUM ( Sales[Quantity] ) )
    )

     

    6) Use it in a table visual:

     

     

1 Reply

  • bcdobbs's avatar
    bcdobbs
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Try the following:

    1) Create a date table:

     

     

    Date = 
    VAR EarliestYear = YEAR ( MIN ( Sales[Date] ) )
    VAR LatestYear = YEAR ( MAX ( Sales[Date] ) )

    RETURN
    ADDCOLUMNS (
        CALENDAR (
            DATE ( EarliestYear, 1, 1 ),
            DATE ( LatestYear, 31, 12 )
        ),
        "Month", DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 1 ) //Format as mmmm YYYY
    )

     

     

     

    2) Set column types as date for both and format Month as mmmm YYYY

    3) Mark it as a date table.

    4) Create relationship to your main table (I called mine Sales)

    5) Create a measure in the main table:

     

     

    Average Monthly Quantity = 
    AVERAGEX (
        VALUES ( 'Date'[Month] ),
        CALCULATE ( SUM ( Sales[Quantity] ) )
    )

     

    6) Use it in a table visual: