Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
iamprajot
Responsive Resident
Responsive Resident

Average Aggregation Problem

Hi

I was testing an Sample Data for 2 Months Moving Average with Blanks in it (Blanks is not the Problem).

The problem is, if we create a Pivot Table from this Data and Sum 2 Months Total then we get the result what is required but what Power BI does is, it gives the Average but it Divides the 2 Months Total with the number of transactions in the Actual Data.

for e.g. Pivot does Jan+Feb/2 but Power BI does Jan+Jan+Feb/3 because of the 2 Transactions in the Jan

here the result from Pivot Table:-

YearMonthTotal2 Months Moving Average
2016Jan999.5999.50
2016Feb719.37859.44
2016Mar167.44443.41
2016Apr448.65308.05

 

And here is the result from Power BI:-

YearMonthTotal Amount2 Months Moving Average
2016Jan999.5999.50
2016Feb719.37572.96
2016Mar167.44295.60
2016Apr448.65205.36

 

What Power BI did is:-

 

 Capture.PNG

Please test this data in Excel and Power BI both.

 

I started achiving below but hit a wall because of above things for which I need answers too.

What I was looking for is,

I need to create an Inner Summarized Table using SUMMARIZE which contains Year, Month, Month Total and 2 Months Moving Average from which I was planning to take the Month Total to get the Average of 2 Months Moving

 

=
ADDCOLUMNS (
SUMMARIZE (
Data,
Data[OrderDate].[Year],
Data[OrderDate].[MonthNo],
"Final Month Total", [Total Amount]
),
"2 Months Moving Average", AVERAGEX (
DATESINPERIOD (
Data[OrderDate].[Date],
LASTDATE ( Data[OrderDate].[Date] ),
-2,
MONTH
),
[Final Total]
)
)

 

Total Amount is an Measure, SUM(Data[Total])

 

Data is here...

 

OrderDateYearMonthRegionRepItemUnitsUnit CostTotal
1/6/162016JanEastJonesPencil  0
1/23/162016JanCentralKivellBinder5019.99999.5
2/9/162016FebCentralJardinePencil364.99179.64
2/26/162016FebCentralGillPen2719.99539.73
3/15/162016MarWestSorvinoPencil562.99167.44
4/1/162016AprEastJonesBinder604.99299.4
4/18/162016AprCentralAndrewsPencil751.99149.25
5/5/162016MayCentralJardinePencil904.99449.1
5/22/162016MayWestThompsonPencil321.9963.68
6/8/162016JunEastJonesBinder608.99539.4
6/25/162016JunCentralMorganPencil904.99449.1
7/12/162016JulEastHowardBinder291.9957.71
7/29/162016JulEastParentBinder8119.991619.19
8/15/162016AugEastJonesPencil354.99174.65
9/1/162016SepCentralSmithDesk  0
9/18/162016SepEastJonesPen Set1615.99255.84
10/5/162016OctCentralMorganBinder288.99251.72
10/22/162016OctEastJonesPen648.99575.36
11/8/162016NovEastParentPen1519.99299.85
11/25/162016NovCentralKivellPen Set  0
12/12/162016DecCentralSmithPencil671.2986.43
12/29/162016DecEastParentPen Set7415.991183.26
1/15/172017JanCentralGillBinder468.99413.54
2/1/172017FebCentralSmithBinder87151305
2/18/172017FebEastJonesBinder  0
3/7/172017MarWestSorvinoBinder  0
3/24/172017MarCentralJardinePen Set504.99249.5
4/10/172017AprCentralAndrewsPencil661.99131.34
4/27/172017AprEastHowardPen  0
5/14/172017MayCentralGillPencil531.2968.37
5/31/172017MayCentralGillBinder808.99719.2
6/17/172017JunCentralKivellDesk  0
7/4/172017JulEastJonesPen Set624.99309.38
7/21/172017JulCentralMorganPen Set5512.49686.95
8/7/172017AugCentralKivellPen Set4223.951005.9
8/24/172017AugWestSorvinoDesk  0
9/10/172017SepCentralGillPencil  0
9/27/172017SepWestSorvinoPen761.99151.24
10/14/172017OctWestThompsonBinder5719.991139.43
10/31/172017OctCentralAndrewsPencil141.2918.06
11/17/172017NovCentralJardineBinder114.9954.89
12/4/172017DecCentralJardineBinder  0
12/21/172017DecCentralAndrewsBinder284.99139.72
1 ACCEPTED SOLUTION
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @iamprajot,

 

In Power BI, you also could get the result as you get in Excel. 

 

However you need to modify your formula like below to get the moving average by 2 month.

 

Before you create the measure, please create a canlendar table and create the relationships between the two tables.

 

Calendar table = CALENDARAUTO()
Moving_Average_2_Months =
CALCULATE (
    AVERAGEX (
        SUMMARIZE (
            'Data',
            'Calendar table'[Date].[Year],
            'Calendar table'[Date].[Month],
            "sumtotal", SUM ( Data[Total] )
        ),
        [sumtotal]
    ),
    DATESINPERIOD (
        'Calendar table'[Date],
        LASTDATE ( 'Calendar table'[Date] ),
        -2,
        MONTH
    )
)

Then you will get the output below.

Capture.PNG

 

Hope this can help you!

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @iamprajot,

 

In Power BI, you also could get the result as you get in Excel. 

 

However you need to modify your formula like below to get the moving average by 2 month.

 

Before you create the measure, please create a canlendar table and create the relationships between the two tables.

 

Calendar table = CALENDARAUTO()
Moving_Average_2_Months =
CALCULATE (
    AVERAGEX (
        SUMMARIZE (
            'Data',
            'Calendar table'[Date].[Year],
            'Calendar table'[Date].[Month],
            "sumtotal", SUM ( Data[Total] )
        ),
        [sumtotal]
    ),
    DATESINPERIOD (
        'Calendar table'[Date],
        LASTDATE ( 'Calendar table'[Date] ),
        -2,
        MONTH
    )
)

Then you will get the output below.

Capture.PNG

 

Hope this can help you!

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

This is exactly what I was looking for. Thanks for going through the whole explanation.

Although it is working without the Calendar Table aswell.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.