Calculating average with condition

[Arthur_NS]

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

    )

[Arthur_NS]

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?

[v-rzhou-msft · Accepted solution]

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.

[amitchandak]

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