Fill Blank Values and Get an Average in Total

[sid-poly]

Please find the table below

School

Jan 2023

Feb 2023

Mar 2023

Apr 2023

May 2023

Jun 2023

July 2023

Aug 2023

Sep 2023

Oct 2023

Nov 2023

Dec 2023

Total

ABC

10

I want to fill the blank with X value and then when they are added I need an average in the total. However, it only shows 10 in the total but not average when I use the below DAX

IF(AVERAGE(X_VAL) = BLANK(),100,AVERAGE(X_VAL))

Please can we fill them with the value and all the values should be used for the computation to get an average in the total column which is auto generated from power BI

[Anonymous · Accepted solution]

Hi @sid-poly 

For your question, here is the method I provided:

Here's some dummy data

“Table”

Please try the following:

First, to fill blank values with specific values, you can use the Power Query editor.

Load the data into Power BI and open the Power Query editor.

Select the columns you want to fill in the blanks for.

Go to the "Convert" tab and select "Replace Values".

In the Value to find field, leave it blank or enter a null value.

In the "Replace with" field, enter the value "X" or any specific value you wish to replace the spaces with.

Click OK to apply the transformation.

Create a measure.

Total = var total = SUMX(
    'Table',
    'Table'[Jan 2023] + 'Table'[Feb 2023] + 'Table'[Mar 2023] 
    + 'Table'[Apr 2023] + 'Table'[May 2023]+ 'Table'[Jun 2023]
    + 'Table'[July 2023] + 'Table'[Aug 2023] + 'Table'[Sep 2023] 
    + 'Table'[Oct 2023] + 'Table'[Nov 2023] +'Table'[Dec 2023]
)

RETURN total / 12

Here is the result.

Regards,

Nono Chen

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

[123abc]

To achieve your goal in Power BI, you can follow these steps:

1. Fill Blank Values with X: You can use the DAX function COALESCE to replace blanks with the X value.

2. Calculate Average in Total Column: Use the AVERAGEX function to calculate the average of non-blank values in each row, and then sum them up in the Total column.

Here's the DAX formula you can use:

Total =
VAR TotalSum =
SUMX (
VALUES ( 'Table'[School] ),
COALESCE ( 'Table'[Jan 2023], 0 )
+ COALESCE ( 'Table'[Feb 2023], 0 )
+ COALESCE ( 'Table'[Mar 2023], 0 )
+ COALESCE ( 'Table'[Apr 2023], 0 )
+ COALESCE ( 'Table'[May 2023], 0 )
+ COALESCE ( 'Table'[Jun 2023], 0 )
+ COALESCE ( 'Table'[July 2023], 0 )
+ COALESCE ( 'Table'[Aug 2023], 0 )
+ COALESCE ( 'Table'[Sep 2023], 0 )
+ COALESCE ( 'Table'[Oct 2023], 0 )
+ COALESCE ( 'Table'[Nov 2023], 0 )
+ COALESCE ( 'Table'[Dec 2023], 0 )
)
RETURN
DIVIDE ( TotalSum, 12 )

This formula computes the sum of each row, replacing blanks with 0, and then divides by 12 to get the average.

Make sure to replace 'Table' with the actual name of your table in your Power BI model. You may also need to adjust column names if they are different in your actual data.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[sid-poly]

@123abc  When I use the COALESCE it doesn't show me any column names and is getting difficult for me to address the issue. I tried using VALUES with COALESCE but that did not work too.

[123abc]

If you're encountering difficulties using COALESCE and VALUES functions in Power BI, there might be a few reasons why you're experiencing issues. COALESCE function isn't directly available in DAX; however, you can achieve similar functionality using other DAX functions like IF and ISBLANK.

Let's break down how you can handle filling blank values and computing the average in the Total column:

1. Fill Blank Values: To fill blank values with 'X', you can use the IF and ISBLANK functions. Here's an example of how you can do it for one column (Jan 2023):

FilledValue_Jan = IF(ISBLANK(SchoolData[Jan 2023]), "X", SchoolData[Jan 2023])

1. You would repeat this step for each month column.

2. Compute Average in Total Column: To compute the average of non-'X' values in each row, you can use the AVERAGEX function along with IF and ISBLANK. Here's how you can do it for the TotalAverage column:

TotalAverage =
AVERAGEX(SchoolData,
IF(NOT(ISBLANK(SchoolData[FilledValue_Jan])),
SchoolData[FilledValue_Jan],
BLANK()
)
)

1. You would replace [FilledValue_Jan] with the appropriate filled value column for each respective month.

By following these steps, you should be able to fill blank values with 'X' and compute the average of non-'X' values in each row for the TotalAverage column.

If you encounter specific errors or issues while implementing these steps, feel free to provide more details, and I'll be happy to assist you further.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.