cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Create a table of measures

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")
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

1 ACCEPTED SOLUTION
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
2 REPLIES 2
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Responsive Resident

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!!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors