Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Mjh
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

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

 

View solution in original post

2 REPLIES 2
v-yangliu-msft
Community Support
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

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

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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