Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
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:
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:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
78 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |