PowerBI DAX how to exclude blanks from weighted average

Asked May 03 '21 at 10:49

Active May 03 '21 at 12:43

Viewed 1,118 times

I can build the weighted average via quick measure

Average of columnA weighted by weight per Id = 
VAR __CATEGORY_VALUES = VALUES('table'[Id])
RETURN
    DIVIDE(
        SUMX(
            KEEPFILTERS(__CATEGORY_VALUES),
            CALCULATE(AVERAGE('table'[columnA]) * SUM('table'[weight]))
        ),
        SUMX(KEEPFILTERS(__CATEGORY_VALUES), CALCULATE(SUM('table'[weight])))
    )

But my columnA has some blanks. How can I exclude these blanks from the weighted average?

edited May 03 '21 at 12:43

asked May 03 '21 at 10:49

Luitpold Wienerle

Would AVERAGEA work in this case https://learn.microsoft.com/en-us/dax/averagea-function-dax – Jon May 03 '21 at 15:25
No. But replacing the second KEEPFILTERS with `FILTER('table', NOT(ISBLANK('table'[columnA])))` worked – Luitpold Wienerle May 04 '21 at 07:53

PowerBI DAX how to exclude blanks from weighted average

0 Answers0