Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
Solved! Go to 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.
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.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
9 | |
7 | |
6 | |
6 |
User | Count |
---|---|
28 | |
11 | |
11 | |
10 | |
6 |