Forum Discussion
Constant Average
I need to have an average remain constant for every month on a visual based on only the first 3 months of the year. Example:
| Month | 3 mo Avg | Net Sales |
Jan | 100 | 100 |
| Feb | 100 | 100 |
| Mar | 100 | 100 |
| Apr | 100 | 58 |
| May | 100 | 54 |
| June | 100 | 56 |
The goal is to use this to calculate a difference between net sales vs. the 3 month average. I have tried several solutions to no avail so now turning to the group who knows best!
Also, please keep in mind that there is underlying data that includes customer, so the 3 month average on a visual should change upon filtering cusotmers.
Customer A:
| Month | 3 mo Avg | Net Sales |
| Jan | 20 | 20 |
| Feb | 20 | 20 |
| Mar | 20 | 20 |
| Apr | 20 | 5 |
| May | 20 | 10 |
| June | 20 | 16 |
My dataset looks like this:
| Customer# | Month | Net Sales |
| 123 | Jan | 56 |
| 124 | Jan | 58 |
| 125 | Jan | 12 |
| 128 | Jan | 900 |
It is a pivot table that has been unpivoted and continues in this manner, duplicating itself back to customer# 123 when it reaches February, and then doing the same through December. I have created a basic month selection table that converts the month to a date to attempt datesbetween (which of course only does the calculation on those 3 months and nothing else), etc. Any help is appreciated.
Hi sagadgreat ,
My sample data is this.
Month
Net Sales
MonthNum
Customer
Year
Jan
100
1
A
2020
Feb
100
2
A
2020
Mar
100
3
A
2020
Apr
58
4
A
2020
May
54
5
A
2020
Jun
56
6
A
2020
Jan
50
1
B
2020
Mar
100
3
B
2020
Apr
54
4
B
2020
May
58
5
B
2020
Jun
56
6
B
2020
Jan
100
1
A
2019
Feb
50
2
A
2019
Mar
100
3
A
2019
Apr
58
4
A
2019
May
54
5
A
2019
Jun
56
6
A
2019
1.Create a measure to get the 3 month average.
3 mo ave = DIVIDE ( CALCULATE ( SUM ( 'Table'[Net Sales] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[Customer], 'Table'[Year] ), [Month] IN { "Jan", "Feb", "Mar" } ) ), 3 )2.Create a measure to get the difference between net sales and the 3 month average.
difference = [3 mo ave]-MAX('Table'[Net Sales])3.The result is as follows. Let the filter select a single select.
You can check more details from here.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
sagadgreat, try this measure:
3 month average = VAR vYear = MAX ( 'Date'[Year] ) VAR vDates = FILTER ( ALL ( 'Date' ), 'Date'[Year] = vYear && 'Date'[Month Number] IN { 1, 2, 3 } ) VAR vResult = CALCULATE ( AVERAGE ( NetSales[Net Sales] ), vDates ) RETURN IF ( ISBLANK ( MAX ( NetSales[Net Sales] ) ), BLANK (), vResult )You'll need a date table that's joined to the net sales table.
Hi sagadgreat ,
My sample data is this.
Month
Net Sales
MonthNum
Customer
Year
Jan
100
1
A
2020
Feb
100
2
A
2020
Mar
100
3
A
2020
Apr
58
4
A
2020
May
54
5
A
2020
Jun
56
6
A
2020
Jan
50
1
B
2020
Mar
100
3
B
2020
Apr
54
4
B
2020
May
58
5
B
2020
Jun
56
6
B
2020
Jan
100
1
A
2019
Feb
50
2
A
2019
Mar
100
3
A
2019
Apr
58
4
A
2019
May
54
5
A
2019
Jun
56
6
A
2019
1.Create a measure to get the 3 month average.
3 mo ave = DIVIDE ( CALCULATE ( SUM ( 'Table'[Net Sales] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[Customer], 'Table'[Year] ), [Month] IN { "Jan", "Feb", "Mar" } ) ), 3 )2.Create a measure to get the difference between net sales and the 3 month average.
difference = [3 mo ave]-MAX('Table'[Net Sales])3.The result is as follows. Let the filter select a single select.
You can check more details from here.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Apologies about getting back to this so late!! Excellent solution. Thanks.
Hi,
Show the exact result that you are expecting.
