CountA and Average of a String Value

samdep — Thu, 01 Jul 2021 13:15:57 GMT

Hi Community!

I have a, hopefully, easy question - and was looking to accomplish via a measure. I have a Subject field - which contains a variety of task types: Initial Attempts, Meeting, Close-Out, etc.

My goal is to count the total number of Initial Attempts - which are categorized as Initial Attempt 1, Initial Attempt 2, and so on, up to 10. I used the below code to get a total, but now I'd like to calculate the average number of Initial Attempts.

Total Attempts =

CALCULATE(

COUNTA(Task[Subject]), Task[Subject] IN

{"Initial Attempt 1", "Initial Attempt 2", "Initial Attempt 3", "Initial Attempt 4", "Initial Attempt 5", "Initial Attempt 6", "Initial Attempt 7", "Initial Attempt 8", "Initial Attempt 9", "Initial Attempt 10"})

I tried making the above a VAR, but was still unable to use the average function - so, I am thinking the best approach then might be to create a condition column that pulls over the numeric value and drops the string (I used RIGHT,2 to do this - not ideal because it left me with some whitespace for single vs two-digit numbers) -- The hurdle I ran into here though is that even though this new column only contains numbers, I receive an error when I try to convert the datatype to whole number.

I've tried the SWITCH function (SWITCH "Initial Attempt 1", 1... etc.), as well as an IF statement --- but still no luck.

I'm basically looking to count up these instances of 'attempts' for an overall total, then by attempt (1,2,3, etc...) -- have both of those needs convered, but I am also looking to answer the question of, on average, how many times does sales reach out/follow-up with a prospect/lead.

Any advice is greatly appreciated! Thanks!

Re: CountA and Average of a String Value

Anonymous — Thu, 01 Jul 2021 14:36:30 GMT

"My goal is to count the total number of Initial Attempts - which are categorized as Initial Attempt 1, Initial Attempt 2, and so on, up to 10. I used the below code to get a total, but now I'd like to calculate the average number of Initial Attempts."

Instead of writing long DAX and remembering to update the code when something changes, you should massage the data in Power Query first and foremost. Create a column (say, Subject Group) in the table and put in a row an entry like "Initial Attempt" if the row contains any of the initial attempts (this new column would be hidden most likely but not necessarily). Then calculating the number of inital attempts will be dead easy. Just count the initial attempts in the context:

[# Initial Attempts] = CALCULATE( COUNTROWS( T ), KEEPFILTERS( T[Subject Group] = "Initial Attempt" ) )

By the way, I don't understand what kind of average you're talking about... To get the number of "Attempt 1" you'd just slice by this entry and the above measure will return the number. Same is true for any other "Attempt."

topic Re: CountA and Average of a String Value in DAX Commands and Tips

CountA and Average of a String Value

Re: CountA and Average of a String Value