Create a table of measures

[Anonymous]

I have a table like this:

survey_id	question_name	answer	person_id
1	question1	yes	1
1	question1	no	2
1	question1	no	3
1	question1	no	4
1	question1	yes	5
2	question1	yes	1
2	question1	yes	2
2	question1	yes	3
2	question1	no	4
1	question2	no	6
1	question2	no	7
1	question2	yes	8
1	question2	yes	9
2	question2	yes	5
2	question2	no	6
2	question2	no	7

So I calculate a simple % of yes answeres per survey. Also, I calculated average of previous measure between surveys.
For %:

%_ =

var tom = CALCULATE(DISTINCTCOUNT(table[person_id]), 'table'[answer] = "yes", question = "question1")

var tot = CALCULATE(DISTINCTCOUNT(table[person_id]), ALL('table'[answer]), question = "question1")

return tom/tot

same for quesion2

Average between surveys:

%Q1_unaided average per survey =

AVERAGEX(KEEPFILTERS(VALUES('table'[survey_id])), CALCULATE([%_]))

The problem, I need to plot it like that:

and be able to filter it by survey, so only blue bar will change, average is static.

I hope I covered all and delivered the problem well. Thanks in advance. ( The visual could be a bit different, it could be a space betwen blue and gray bars

[v-shex-msft · Accepted solution]

HI @Anonymous,

Did you mean to modify the formula to auto-fit each question types in your formula? If that is the case, you can try to use following measure formula to replace the raw percentage formula:

%_ =
VAR tom =
    CALCULATE (
        DISTINCTCOUNT ( table[person_id] ),
        FILTER ( 'table', 'table'[answer] = "yes" ),
        VALUES ( 'table'question )
    )
VAR tot =
    CALCULATE (
        DISTINCTCOUNT ( table[person_id] ),
        ALLSELECTED ( 'table' ),
        VALUES ( 'table'question )
    )
RETURN
    tom / tot

Regards,

Xiaoxin Sheng

[Jayee]

Hi @Anonymous 

Try this measure

%Q1_unaided average per survey =

AVERAGEX(KEEPFILTERS(VALUES('table'[survey_id])), CALCULATE([%_],all(question)))

If this post helps, then please consider Accept it as the solution, Appreciate your Kudos!!