cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
YJAMOUS
Helper III
Helper III

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
1 ACCEPTED SOLUTION
bcdobbs
Super User
Super User

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

bcdobbs_0-1643209200959.png

3) Mark it as a date table.

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

bcdobbs_1-1643209246505.png

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:

bcdobbs_2-1643209475094.png

 

 



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

View solution in original post

1 REPLY 1
bcdobbs
Super User
Super User

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

bcdobbs_0-1643209200959.png

3) Mark it as a date table.

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

bcdobbs_1-1643209246505.png

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:

bcdobbs_2-1643209475094.png

 

 



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Helpful resources

Announcements
Power BI Show Ep 4 Post Show Carousel.jpg

The Power BI Community Show

Shabnam Watson demos Incremental refresh & Hybrid Tables and Leila Etaati demos Charticulator.

PBI April Release 2022 768x460.png

Check it out!

Click here to read more about the April 2022 updates!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!