Forum Discussion
Average and Count in same Graph
Hi Anonymous,
I think you can enter to query editor to add a column to check type and filter your formula to calculate average of rows which 'isnumber' is 'true'.
Sample:
Query:
= Table.AddColumn(#"Changed Type", "Isnumber", each try Number.FromText([Test]) is number otherwise false)
Result:
Regards,
Xiaoxin Sheng
I have one problem, the field I am trying to avg and count has some numbers and string values in it. I am only looking to average and count the number ones. I tried filtering the visual but I am pulling this directquery and telling me average is not used. I turned on the allwo restricted but it is still having the trouble with the string values.
Hi Anonymous,
I'm no very clear for your description, can you please share the sample pbix file for test?
>> I am only looking to average and count the number ones.
If you want to deal with numeric part, I'd like to suggest you enter to query editor and add a custom column to store splitted numeric part to calculate.
PowerQuery | Extract Numbers from A string (eg ABCD1234)
Regards,
Xiaoxin Sheng
I see how this is very useful, but my problem us the field could have an answer of a number in one row and another string, not together in the same field.
Yes
0
1
14:35
Nothing seen here
1
1
0
1/1/15
That is the problem. I am just trying to average the numbers for a specific entry.
Hi Anonymous,
I think you can enter to query editor to add a column to check type and filter your formula to calculate average of rows which 'isnumber' is 'true'.
Sample:
Query:
= Table.AddColumn(#"Changed Type", "Isnumber", each try Number.FromText([Test]) is number otherwise false)
Result:
Regards,
Xiaoxin Sheng
I did try this and it was working for on of my dashbaords, but the one I am trying to use it on is a DirectQuery and when I went into the Advanced Query Editor, it told me I could not do this adding a new query. Am I placing this in the wrong spot on the file. Please advise. Thanks