Average of parameter

[sheng]

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

[johncolley · Accepted solution]

Hi @sheng ,

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!

[sheng]

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!