Forum Discussion

Arthur_NS's avatar
Arthur_NS
Icon for Helper I rankHelper 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 Share Individuals but all lines have a BT.
So the end result is wrong.
What I need is the same calculation that filters out the lines with no Share Individuals, so that the BT is not taken into account for the lines without Share Individuals.
Any idea how to integrate this?

 

Market share =
VAR __CATEGORY_VALUES = VALUES('MM'[Share Individuals 3+])
RETURN
    DIVIDE(
        SUMX(
            KEEPFILTERS(__CATEGORY_VALUES),
            CALCULATE(SUMX('MM','MM'[Share Individuals 3+]*MM[BT]))
        ),
        SUMX(KEEPFILTERS(__CATEGORY_VALUES), CALCULATE(SUM('MM'[BT])))
    )
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.

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Arthur_NS , check if this exmaple can help

     

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

     

     

    or

     

    Market share =
    VAR __CATEGORY_VALUES = VALUES('MM'[Share Individuals 3+])
    RETURN
    DIVIDE(
    SUMX(
    KEEPFILTERS(__CATEGORY_VALUES),
    CALCULATE(SUMX(filter('MM','MM'[Share Individuals 3+]<> 0 && not(isblank('MM'[Share Individuals 3+] )) ),'MM'[Share Individuals 3+] *MM[BT]))
    ),
    SUMX(KEEPFILTERS(__CATEGORY_VALUES), CALCULATE(SUM('MM'[BT])))
    )

  • Arthur_NS's avatar
    Arthur_NS
    Icon for Helper I rankHelper 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.