Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am not able to include sample files so I hope I can explain this correctly. I have a table with a column that the responses can range amoung several different answers. What I am trying to do is some of them I want to lump under the same answer but still graph off of this number. I tried putting them together under the same response using an IF but it is adding them all together. I am looking for an average for them.
Example
Response Count
1b 2
1c 1
1e 5
1d 4
2
3
4
5
6
7
1a 3
I want all the 1's to be listed as 1 and instead of giving me back a total of 15 I want the average of all of them but still be able to give the counts for the remainder 2 - 7. I hope I am explaining this well enough. Thanks for any help you can give.
Solved! Go to Solution.
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
Hi @Anonymous,
You can try to use below measure to modify the total row to show average count:
AVG Count = IF ( COUNTROWS ( 'Sample' ) = COUNTROWS ( ALLSELECTED ( 'Sample' ) ), AVERAGEX ( SUMMARIZE ( ALLSELECTED ( 'Sample' ), [Response], "Count", COUNT ( 'Sample'[Response] ) ), [Count] ), COUNT ( 'Sample'[Response] ) )
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
HI @Anonymous,
It seems like direct query not support to add custom columns. I haven't found other ways to work through this issue.
Regards,
Xiaoxin Sheng
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
102 | |
84 | |
79 | |
68 |
User | Count |
---|---|
120 | |
110 | |
94 | |
81 | |
77 |