cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## Weighted averages for more than one category

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

1 ACCEPTED SOLUTION
Regular Visitor

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] )
)```

2 REPLIES 2
Community Support

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

Regular Visitor

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] )
)```