DAX: Averaging multiple % Columns

Question

I'm new to Power BI and Dax, having some difficulty with the below scenario.

test    a       b        c       d           AVERAGE
aa              51.97%           46.61%      49%

I have 4 columns, a-d, and I simply want the average of the 4 columns in the AVERAGE column. Dependent on the row different columns may be blank. Each of the columns are measures pulling through a % value into the table.

I'm sure there must be a simple solution to this but any help would be much appreciated.

The blank columns could affect the average calculation, is it ok? — alejandro zuleta, Feb 24 '17 at 14:56

score 3 · Accepted Answer · edited Mar 07 '20 at 11:36

3

Try creating a column like this:

AVERAGE = ([a]+[b]+[c]+[d])/4

UPDATE: BLANK measures don't affect average result.

AVERAGE = DIVIDE(([a]+[b]+[c]+[d]),
  (IF(ISBLANK([a]),0,1) + IF(ISBLANK([b]),0,1) + 
   IF(ISBLANK([c]),0,1) + IF(ISBLANK([d]),0,1)))

edited Mar 07 '20 at 11:36

halfer

19,824
17
99
186

answered Feb 24 '17 at 14:59

alejandro zuleta

13,962
3
28
48

Thanks for the response. I had done that originally but I need the average function to only look at entries which are not blank otherwise it will skew the results. – ScotCal Feb 24 '17 at 15:00
@ScotCal, I've updated my answer try my second expression. – alejandro zuleta Feb 24 '17 at 15:11
@ScotCal, You are welcome, If my answer solved you issue you can [mark it as the correct answer](http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work) to positively close the question. – alejandro zuleta Feb 24 '17 at 15:22

DAX: Averaging multiple % Columns

1 Answers1