How to Calculate Weighted Average Sales per Month using DAX in Power BI

How to Calculate Weighted Mean Sales per Month using DAX in Power BI

Scenario:

Suppose we have a Data Model as follows:

The Relations are as follows:

Based on the above Model, we will calculate the Weighted Average Sales per Month in a Year.

To calculated Weighted Average Sales, we need to calculate Weight of the Monthly Sales with respect to Total Sales in a Year.

Eg: [Monthly Sales Weight] = [Monthly Sales] / [Yearly Sales]

Next will multiply the [Monthly Sales Weight] with [Monthly Sales] to get the Weighted Average Sales per Month.

To achieve the Above Scenario, we need to use the below DAX Measures/Logics.

Net Sales =

        SUMX ( tbl_Sales, ( tbl_Sales[Unit_Price] * tbl_Sales[Units_Sold] )) 

Yearly Sales =

        CALCULATE([Net Sales],ALLEXCEPT(tbl_Calendar,tbl_Calendar[Year]))

The below Measure calculates the Weight of Month Sales over Yearly Sales:

Monthly Sales Weigh =

VAR vYearlySales =

            CALCULATE([Net Sales],ALLEXCEPT(tbl_Calendar,tbl_Calendar[Year]))

RETURN

DIVIDE([Net Sales], vYearlySales)

The below Measure calculates the Weighted Average Month Sales over Yearly Sales:

Monthly Weigh Avg Sales =

VAR vYearlySales =

            CALCULATE([Net Sales],ALLEXCEPT(tbl_Calendar,tbl_Calendar[Year]))

VAR vMonthlyWeigh = DIVIDE([Net Sales], vYearlySales)

RETURN

[Net Sales] * vMonthlyWeigh

The below Measure calculates the Cumulative Weighted Average Month Sales:
Monthly Weigh Avg Cum Sales =

VAR vYearlySales =

            CALCULATE([Net Sales],ALLEXCEPT(tbl_Calendar,tbl_Calendar[Year]))

VAR vMaxMonth = MAX(tbl_Calendar[Month_No])

RETURN

CALCULATE(

    [Net Sales] * DIVIDE([Net Sales], vYearlySales),

    tbl_Calendar[Month_No]<=vMaxMonth,

    ALLSELECTED(tbl_Calendar[Month_Name], tbl_Calendar[Year])                

    )

Now we can see the result of all the Measures as per below:

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------