cancel
Showing results for
Did you mean:
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 Description Customer Average Monthly Quantity 123456 Pencils Staples 123456 Pencils Walmart 123456 Pencils Costco 654789 Sharpner Staples 654789 Sharpner Walmart 654789 Sharpner Costco 987456 Ink Staples 987456 Ink Walmart 987456 Ink Costco

My Sample Data looks like this

 Date Material# Material Description Customer Quantity 1/1/2021 123456 Pencils Staples 100 1/1/2021 654789 Sharpner Walmart 400 25/1/2021 654123 Paper Costco 300 1/3/2021 123456 Pencils Walmart 300 2/3/2021 987456 Ink Staples 500 15/3/2021 654789 Sharpner Costco 600 2/4/2021 123456 Pencils Staples 100 10/4/2021 654123 Paper Walmart 400 15/4/2021 987456 Ink Staples 300 10/5/2021 987456 Ink Costco 200 15/5/2021 123456 Pencils Costco 500 20/5/2021 654789 Sharpner Staples 100 7/7/2021 654123 Paper Costco 200 8/7/2021 123456 Pencils Costco 500 20/7/2021 987456 Ink Walmart 400 2/8/2021 654789 Sharpner Staples 300 24/08/2021 123456 Pencils Walmart 100 15/9/2021 987456 Ink Costco 200 20/10/2021 123456 Pencils Staples 200 25/10/2021 654123 Paper Costco 300 29/10/2021 123456 Pencils Staples 200 17/11/2021 654789 Sharpner Walmart 600 20/11/2021 987456 Ink Walmart 200 27/11/2021 654789 Sharpner Costco 400 10/12/2021 123456 Pencils Walmart 400 15/12/2021 654123 Paper Costco 300 16/12/2021 123456 Pencils Walmart 200
1 ACCEPTED SOLUTION
Super User

Try the following:

1) Create a date table:

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

RETURN
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:

Ben Dobbs

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

Try the following:

1) Create a date table:

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

RETURN
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:

Ben Dobbs

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

Announcements

#### The Power BI Community Show

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