Weighted averages for more than one category

[Mjh]

Hi

I have a matrix outlining average handle times per phone call, per day (I don’t have access to the raw call by call data). I'm wanting to look at the data over time and have it be able to be rolled up/down for day/week/month and also by hierarchy e.g. 5 teams roll up together into one view.

I have got a formula to do one or the other (see below relating to a team roll up), but have been unable to weight by both hierarchy and date at the same time. Is there a way to alter this so that 2 different category values can be used simultaneously?

formula =

VAR __CATEGORY_VALUES = VALUES('CapPlan'[Team])

RETURN

    DIVIDE(

        SUMX(

            KEEPFILTERS(__CATEGORY_VALUES),

            CALCULATE(

                SUM('CapPlan'[Raw Forecasted Call AHT])

                    * SUM('CapPlan'[Forecasted Calls])

            )

        ),

        SUMX(

            KEEPFILTERS(__CATEGORY_VALUES),

            CALCULATE(SUM('CapPlan'[Forecasted Calls]))

        )

    )

many thanks

[v-yangliu-msft]

Hi  @Mjh ,

If convenient, you can present the expected result as a picture, and can you share sample data and sample output in a tabular format? or delete sample PBIX after sensitive data. We can better understand the problem and help you.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Mjh · Accepted solution]

Hi

thanks for getting back to me. ive actually managed to work this out, with the help of another similar question - Solved: Calculation of weighted average by multiple groups - Microsoft Fabric Community

For this to work I created a basic measure for the average figure at the lowest level available first, then place that into the below formula along with the weighting value. in the example below you'd be weighting the price by volume per category.

Measure =
DIVIDE (
    SUMX ( Table1, Table1[count] * Table1[price] ),
    SUMX ( Table1, Table1[count] )
)