How to calculate Average of Text column using DAX in Power BI
A quick post today about how to calculate Average for a column with text data type in Power BI.
In Power BI, you can only count the the number of rows in a column that has a text data type under summarization, other summarizations are not available for a text data type.
If you attempt to use an Average function in a measure for a text column, you will get an error message like the one below:
To calculate the Average of a text column, you divide the count of the column by the distinct count using a DAX measure. For the below example, I have 2 columns with text data types and I want the average of “count of products”.
- To see the Total Count of Products, I used Count for summarization.
2. To see Distinct Count of Products, I used Count Distinct for summarization.
3. Now to see the Average, I divide the Count by the Distinct Count as seeing below.
Happy data modelling 🙂
2 thoughts on “How to calculate Average of Text column using DAX in Power BI”
This fixed my issue Ruki thanks for sharing. Average function will definitely not work in this case because the column is a text.
Your blog has been one of my go to resources each time I have issues with power bi project. This came in handy, thank you so much.
Comments are closed.