cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
viitama Frequent Visitor
Frequent Visitor

weighted average for groups

Hi, I have simple calculation, that I thought would work with filtering, but I can't get it to work. My data:

Capture.PNGTable1

I thought that my formula for result fieldwould work when slicing with groups but it doesn't.

Result = (Table1[Value] / sum(Table1[Value]) * Table1[X])

Correct results calculated with excel are -13.333 for group A and -35.5556 for group B. I assume that my sum functions doesn't work in filtering but how do I write this that weight part is only calculated for grouped part?

 
 
 
 
 
 
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: weighted average for groups

Hi @viitama,

 

Please try this measure:

Result =
SUMX (
    Table1,
    CALCULATE (
        MAX ( Table1[Value] )
            / CALCULATE ( SUM ( Table1[Value] ), ALLEXCEPT ( Table1, Table1[Group] ) )
            * MAX ( Table1[X] )
    )
)

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 REPLY 1
Community Support Team
Community Support Team

Re: weighted average for groups

Hi @viitama,

 

Please try this measure:

Result =
SUMX (
    Table1,
    CALCULATE (
        MAX ( Table1[Value] )
            / CALCULATE ( SUM ( Table1[Value] ), ALLEXCEPT ( Table1, Table1[Group] ) )
            * MAX ( Table1[X] )
    )
)

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.