Forum Discussion
Average of parameter
Hi all,
I am trying to create a card visual that can be filtered by a field parameter. I created a field parameter (Test parameter) that contains 3 question fields (Q1, Q2, Q3). The card visual should show the AVERAGE score of the question that is selected in Test parameter. I tried creating a measure with the following DAX formula: Average score = AVERAGE(Test parameter[Test Parameter Fields]) but when I pull this measure into the card visual it says "The function AVERAGE cannot work with values of type String." I am certain the Q1, Q2, and Q3 are numeric fields so can someone let me know what I am doing wrong and how can I get this visual to work please?
I attached a sample dataset, let's call this [Test]. Hopefully this could help in answering my question!
| TEST ID | Q1 | Q2 | Q3 | TEST TYPE |
| 123 | 3 | 1 | 1 | PRE |
| 124 | 1 | 2 | 4 | PRE |
| 125 | 2 | 1 | 2 | PRE |
| 126 | 5 | 3 | 4 | POST |
| 127 | 4 | 5 | 2 | POST |
| 128 | 2 | 4 | 1 | POST |
Thank you all in advance!
Sheng
Hi Anonymous ,
You are receiving that error because you are trying to average the Test Parameter Fields column if you look at that column you will see it's a text field.
You should create a measure to calculate the average of each question and then use those measures in your parameter. For example,
Avg Q1 = average(Q1)
Avg Q2 = average(Q2)
Avg Q3 = average(Q3)
Then use Avg Q1, Avg Q2 & Avg Q3 as the parameter options.
If this answer solves your problem please mark it as a solution!
2 Replies
- johncolley
Solution Sage
Hi Anonymous ,
You are receiving that error because you are trying to average the Test Parameter Fields column if you look at that column you will see it's a text field.
You should create a measure to calculate the average of each question and then use those measures in your parameter. For example,
Avg Q1 = average(Q1)
Avg Q2 = average(Q2)
Avg Q3 = average(Q3)
Then use Avg Q1, Avg Q2 & Avg Q3 as the parameter options.
If this answer solves your problem please mark it as a solution!
It worked. Thank you very much!
I have a follow-up question that could use some help too - If I have another frequency chart that is filtered based on the question fields themselves instead of the average, would it be possible to create a field parameter that filters both the card visual mentioned in my initial question and this frequency chart?
This frequency chart shows the count of distinct score for each question (imagine x-axis as distinct score 1,2,3,4,5 and y-axis as frequency).
Thank you again for your help!