Power BI | In Line Chart persentage of category data

Question

I want to show my data in line chart as persentage of the category. I have handled users' persentage data with "show value as" > "Persentage of Column Total" in this matris visulation.

wrong visulation

In this image you can see, I have users' distict count, survey name and answertext. I want to show this persentage of distinct count user data by SurveyName in answertext lines. So I need line chart like this:

true visulation

Answers Table:

ResponseID  QuestionID  QuestionText    AnswerID    AnswerText
XYZ1        Q1          qtext1          q1a1        q1a1text
XYZ1        Q2          qtext2          q2a1        q2a1text
XYZ1        Q3          qtext3          q3a1        q3a2text
XYZ2        Q1          qtext1          q1a2        q1a2text
XYZ2        Q3          qtext3          q3a1        q3a1text
XYZ3        Q1          qtext1          q1a1        q1a1text
XYZ3        Q2          qtext2          q2a2        q2a2text
XYZ3        Q3          qtext3          q3a2        q3a2text
XYZ4        Q1          qtext1          q1a1        q1a1text
XYZ4        Q2          qtext2          q2a1        q2a1text
XYZ4        Q3          qtext3          q3a2        q3a2text
XYZ5        Q1          qtext1          q1a3        q1a3text
XYZ5        Q2          qtext2          q2a1        q2a1text
XYZ6        Q2          qtext2          q2a3        q2a3text
XYZ6        Q3          qtext3          q3a1        q3a1text
XYZ7        Q1          qtext1          q1a2        q1a2text
XYZ7        Q3          qtext3          q3a2        q3a2text
XYZ8        Q1          qtext1          q1a1        q1a1text
XYZ8        Q2          qtext2          q2a2        q2a2text
XYZ8        Q3          qtext3          q3a1        q3a1text

Responses Table:

SurveyID    SurveyName  UserID  ResponseID
SV_1234     survey1     ABC1    XYZ1
SV_1234     survey1     ABC4    XYZ2
SV_1234     survey1     ABC2    XYZ3
SV_1234     survey1     ABC3    XYZ4
SV_5678     survey2     ABC1    XYZ5
SV_5678     survey2     ABC4    XYZ6
SV_5678     survey2     ABC3    XYZ7
SV_9821     survey3     ABC1    XYZ8

The critical problem is to distribute the number of users participating in more than one survey according to the answers and the survey. So here I need to find common users who participated in the selected surveys and then give the distribution of these users according to the answers in the questions in the selected surveys.

Answer 1

First of all, we need to calculate UserID, not responseID.

Here we will first select multiple polls as you can see below, and then find the number of common users who participated in those polls. Later, we will give the distribution of these common users according to SurveyNames on axis as a percentage. and of course here the sum of UserID percentages in AnswerText's for each SurveyName on axis should be 100.

enter image description here

For this, I first combined the response table and the answer table. Then I typed a measurement as below to find the number of common users who participated in the selected surveys. However, when I add the UserID number to linechart, it does not give common users, it only gives the total UserID participating in the relevant survey.

 Count of Cohort 7c = 
    VAR __tmpTable1 = 
        CALCULATETABLE(
            GENERATE(
                DISTINCT('Table (7)'[UserID]),
                EXCEPT(
                    DISTINCT('Table (7)'[SurveyName]),
                    CALCULATETABLE(DISTINCT('Table (7)'[SurveyName]))
                )
            ),
            REMOVEFILTERS('Table (7)'[AnswerText])
        )
    VAR __tmpTable2 = SUMMARIZE(__tmpTable1,[UserID])
    VAR __tmpTable3 = EXCEPT(DISTINCT('Table (7)'[UserID]),__tmpTable2)
    VAR __CountMax = COUNTROWS(__tmpTable3)

    VAR __Survey = SELECTCOLUMNS('Table (7b)',"__SurveyName",[SurveyName])
    VAR __Question = SELECTCOLUMNS('Table (7a)',"__QuestionText",[QuestionText])
    VAR __tmpTable4 = DISTINCT(SELECTCOLUMNS(FILTER('Table (7)',[SurveyName] IN __Survey && [QuestionText] IN __Question),"UserID",[UserID]))
    VAR __CountMin = COUNTROWS(__tmpTable4)
    VAR __tmpTable5 = FILTER(__tmpTable4,[UserID] IN __tmpTable3)
    VAR __Count = COUNTROWS(__tmpTable5)
RETURN
    SWITCH(TRUE(),
        ISBLANK(COUNTROWS(__tmpTable4)),BLANK(),
        __CountMin < __Count,__CountMin,
        __Count > __CountMax,__CountMax,
        __Count
    )

enter image description here

If you want I can send my power bi report.

Answer 2

First, let your both tables are connected using column ResponseID. Now, Create this below Custom Column First-

SurveyName = RELATED(Responses[SurveyName])

Now create a Measure as below-

percentage = 

VAR current_survey_name = MIN(Answers[Surveyname])

VAR distict_resp_id_all_text = 
CALCULATE(
    DISTINCTCOUNT(Answers[ResponseID]),
    ALLEXCEPT(
        Answers,
        Responses[SurveyName]
    )
)

VAR distict_resp_id_per_text = 
CALCULATE(
    DISTINCTCOUNT(Answers[ResponseID]),
    ALLEXCEPT(
        Answers,
        Answers[AnswerText],
        Responses[SurveyName]
    )
)

RETURN distict_resp_id_per_text/distict_resp_id_all_text

Here is the final output in line chart for your given sample data-