Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Arioli_Chezhian
Helper II
Helper II

How to get average of text column?

Hi All,

I have a column which has 'user name' and it's datatype is 'text'. Currently I am showing the data in card visual which shows distinctcount of username.

However, I would like to show 'Average of number of users'. Since it is in text datatype, could you please advice on how to write the measure to achieve this scenario?

E.g. data:

Username
abc
def
ghi
1 ACCEPTED SOLUTION
Anonymous
Not applicable

@Arioli_Chezhian  This measure for example will calculate the average number of products, year over year. I recommend trying quick measures to learn things like this.

Average # of Products YOY =
AVERAGEX(
    KEEPFILTERS(VALUES('Date Table'[Year])),
    CALCULATE(COUNTA('Sheet1'[Product]))
)
bchager6_1-1715529479142.png

 


 

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

@Arioli_Chezhian  What would your average be based on? For example, average number of users over time or average sales by user.

 

@Anonymous  - In a card visual, just need to show the Average of number of users. May be you can consider as average number of users over time.

Anonymous
Not applicable

@Arioli_Chezhian  This measure for example will calculate the average number of products, year over year. I recommend trying quick measures to learn things like this.

Average # of Products YOY =
AVERAGEX(
    KEEPFILTERS(VALUES('Date Table'[Year])),
    CALCULATE(COUNTA('Sheet1'[Product]))
)
bchager6_1-1715529479142.png

 


 

@Anonymous  - Thank you for your response.

Is it possible to get average of distinctcount instead of count?

Anonymous
Not applicable

Try replacing COUNTA with DISTINCTCOUNT

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

Top Solution Authors