Problem with average counting

[ocin33]

Hello,

I have a problem with the counting average in Power BI. The whole process looks like this:

1. I have a form in MS Forms where user can give max. 4 stars (when no star is marked it is marked as blank cell both in Sharepoint and PowerBI). In Forms there in probably no option to choose "not applicable" when I use grades.

2. Forms is related with Sharepoint list

3. Sharepoint list is related with Power BI

In PowerBI I have to get average of particular values so I have added a column and put simple formula. Unfortunately, Power BI takes the blank cell as a "0" so the average is not like in the MS Excel where the empty cell is not counted in average.

 

Do you have any idea/hint how can I modify my tools to count the proper average value (empty cell means not applicable and it should not be taken into account in the formula)?

[AllisonKennedy]

@ocin33  It looks like you're trying to calculate average of multiple columns, so not using the AVERAGE function (which would give the results you're looking for). Because you are doing 

/4 

in your formula, it will always count all four columns. You need to change the 4 to be more dynamic.

As a quick solution you can try something like: 

Average Column = 

DIVIDE( Column1 + Column2 + Column3 + Column 4 ,

IF(ISBLANK(Column1), 0, 1) + IF(ISBLANK(Column2), 0, 1) + IF(ISBLANK(Column3), 0, 1) + IF(ISBLANK(Column4), 0, 1)

)

But ultimately I recommend you step back and look at your data model as a whole. What else do you need to do with these columns? Often when I import data from Microsoft Forms into Power BI I find myself creating multiple tables from the 1 forms table in order to get the right set of Dimension tables (UserID, Date form completed, short answer questions, etc) and Fact tables (answers to questions where multiple selections are allowed, the four questions you have asked here that need to be averaged, etc). The Fact tables need an unpivot, so I typically:
Open Power Query Editor by clicking Transform Data in Home tab.

Right click on the Forms table in left hand side and Duplicate

Rename the Duplicate Query to FactStarRating 

Click Choose Columns in the Home tab in the ribbon and select ID, Column1, Column2, Column3, Column4

Select the ID column.

Click Unpivot Other Columns in the Transform tab.

Rename Attribute column to Question.

Rename Value column to Rating. (or whatever makes sense to you)

Click Close and Apply in Home tab.

Click the Model icon on left of Power BI to edit relationships.

Drag ID column from FactStarRating to ID column in Forms table to create a 1 to many relationship between them.

Click the Report icon to get back to the report canvas.

Click the three dots next to FactStarRating table and select New Measure: 

Average Rating = AVERAGE( FactStarRating[Rating] )

Hit Enter to save the above Measure.

Tick the box next to the Average Rating Measure.

Tick the box next to the Forms table ID (NOT the FactStarRating ID)

Select the Matrix visual to view these in a table.

Do other stuff as required.

Hope that makes sense, otherwise reach out if not. 

[ocin33]

Thanks for this tipps. I will try to use it and give the feedback 🙂

[amitchandak]

@ocin33 , Try like

calculate(Average(Table[column]),not(isblank(Table[Column])))

[ocin33]

Thank you for the reply. Unfortunatelly I do not fully understand how should it looks like. Could you please write this formula based on following, four columns?:

 You can write column 1,2 and so on not to write everything. The most problematic for me are proper brackets, semicolons...
Sorry for that but I am completely new in PowerBI topics 😕