Constant Average

[sagadgreat]

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.  

[Ashish_Mathur]

Hi,

Show the exact result that you are expecting.

[v-stephen-msft · Accepted solution]

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.

[sagadgreat]

Apologies about getting back to this so late!!  Excellent solution. Thanks.

[DataInsights]

@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.