Count the number of values in a row across columns

[jcarbo0321]

Hello -

I have a table like the one here: 

And I would like to create a measure or a calculated column or anything that would return the average of a row across columns, ignoring the blanks. Ideally, this would mean that a row with blanks would be calculated as an average of the true numbers across the row. For example, in the table in Excel where the above information can be found, I made a column with formula: 

=AVERAGE([Jan FTE]:[Dec FTE]) which always calculates correctly, but I would rather it be a DAX measure or column if at all possible. Mainly because Power Pivot is determining the type of these columns as text and I can't get it to change despite many tries. 

Is there an easy way to do this in DAX? 

[Anonymous · Accepted solution]

Hi @jcarbo0321 ,

Power bi DAX calculations are for columns, if you want to achieve this idea, I suggest you first transform the data.

Based on your description, I create a simple sample:

The first step is to create an index for the table in order to pivot the columns:

Then pivot column:

This can be accomplished using the index field and measure and below is the result:

Measure = AVERAGE('Table'[Value])

Perhaps this will work, and if you have any questions, please provide me with more information to make sure we can better solve the problem for you!

An attachment for your reference. Hope it helps.

Best regards,

Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.