How to Calculate Weighted Average Sales by Product over Year using DAX

How to Calculate Weighted Average Sales by Month over Year using DAX

Scenario:

Suppose we have a data Model as follows:

The relationships are as follows:

1) Weighted Average Sales by Month over Year:

We can calculate the Weighted Average Sales by Month over Year using below DAX Logics.

Net Sales = Sum(fct_Orders_Sample[Net_Sales])

Yearly Sales =

VAR _Annual_Sales =

CALCULATE(

    [Net Sales],

    ALLEXCEPT(dim_Calendar, dim_Calendar[DimYear])      

    )

VAR _Result = IF ( NOT(ISBLANK([Net Sales])), _Annual_Sales)

RETURN

_Result

Weighted Avg = DIVIDE([Net Sales], [Yearly Sales])

Weigh Avg Sales by Month =

VAR _SalesSummary =

    ADDCOLUMNS(

        SUMMARIZECOLUMNS(

            dim_Calendar[DimYear],

            dim_Calendar[MonthName],

            "NetSales", [Net Sales]        

        ),

   

    "WeighAvgByMonth",

        DIVIDE(

            [NetSales],

            CALCULATE(

                [Net Sales],

                ALLEXCEPT(dim_Calendar, dim_Calendar[DimYear]),

                ALLEXCEPT(dim_Calendar, dim_Calendar[MonthNo])

            )

        )

    )

VAR _Result =

    SUMX(_SalesSummary, [NetSales]*[WeighAvgByMonth])

RETURN

_Result

Result:

2) Weighted Average Sales by Product over Year:

We can calculate the Weighted Average Sales by Product using below DAX Logics.

Weigh Avg Sales by Product =

VAR _SalesSummary =

    ADDCOLUMNS(

        SUMMARIZECOLUMNS(

            dim_Calendar[DimYear],

            dim_Products[Product_ID],

            "NetSales", [Net Sales]        

        ),

   

    "WeighAvgByProd",

        DIVIDE(

            [NetSales],

            CALCULATE(

                [Net Sales],

                ALLEXCEPT(dim_Calendar, dim_Calendar[DimYear]),

                ALLEXCEPT(dim_Products, dim_Products[Product_ID])

            )

        )

    )

VAR _Result =

    SUMX(_SalesSummary, [NetSales]*[WeighAvgByProd])

RETURN

_Result

Result:

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

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

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