Forum Discussion

Arthur_NS's avatar
Arthur_NS
Helper I
4 years ago
Solved

Calculating average with condition

Hi everyone,   I created a new measure with the below code. It basically creates a weighted average of Share Individuals 3+ based on BT.   The thing is that in my database, not all lines have a ...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

     

Arthur_NS's avatar
Arthur_NS
Helper I
4 years ago

Not working, values are too low which leads me to believe too many lines are still taken into account

Shouldn't the MM BT be filtered as well?

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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.