Calculating average with condition

Hi Arthur_NS ,

I think your problem should be caused by _Part2. It sums all BT, so return incorrect result in divide. I suggest you to add a filter in __CATEGORY_VALUES. Here I create a sample to have a test.

Try this code.

Market share =
VAR __CATEGORY_VALUES =
    FILTER (
        VALUES ( 'MM'[Share Individuals 3+] ),
        MM[Share Individuals 3+] <> BLANK ()
    )
VAR __PART1 =
    SUMX (
        KEEPFILTERS ( __CATEGORY_VALUES ),
        CALCULATE ( SUMX ( 'MM', 'MM'[Share Individuals 3+] * MM[BT] ) )
    )
VAR __PART2 =
    SUMX ( KEEPFILTERS ( __CATEGORY_VALUES ), CALCULATE ( SUM ( 'MM'[BT] ) ) )
RETURN
    DIVIDE ( __PART1, __PART2 )

I think the result you want is (3*4+1*2+5*4+7*8)90 / 18 (4+2+4+8 ) = 5.

Best Regards,
Rico Zhou

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