persentage of category data in Line Chart

[Anonymous]

I have a problem with the AND functionality in the Power BI Slicer. This problem prevents me from giving percentage userID numbers by category in the line chart. There was nobody who could answer this task I opened in Community. Can you help me?

https://community.powerbi.com/t5/Desktop/In-Line-Chart-persentage-of-category-data/m-p/1369483

[v-lili6-msft]

hi  @Anonymous 

For your case, you need to create a percent measure as below:

Measure =
DIVIDE (
    COUNTA ( 'Responses Table'[UserID] ),
    CALCULATE (
        COUNTA ( 'Responses Table'[UserID] ),
        ALLSELECTED ( 'Answers Table'[AnswerText] )
    )
)

just use this measure in Values instead of set show value as>

Result:

Regards,

Lin

[Anonymous]

Hi @v-lili6-msft 

Thank you for your response. Actually firstly, I need calculate userID count which participating selected one more surveys.

For example, I select survey1 and survey2 in surveyName slicer. In this case we must calculate userID is 3 because ABC1, ABC3 and ABC4 participated both survey1 and survey2. And then we must calculate this 3 users' persentage in selected surveys and selected questions.

[v-lili6-msft]

hi  @Anonymous 

here is my sample pbix file, please try it.

https://qiuyunus-my.sharepoint.com/:u:/g/personal/linli_qiuyunus_onmicrosoft_com/EbE-KvjmxFNBqAgHpUUUgXABXHAldDB6yzbkaQ7jjSUAbw?e=kVUIFC

if not your case, what is your expected output?

Regards,

Lin

[Anonymous]

Hi @v-lili6-msft 

Firstly, I must select surveys. Can you examine below image? I need an output like this:

Regards,

Fırat

[v-lili6-msft]

hi  @Anonymous 

You made your choice twice, the first result will not be recorded.

and i'm still confused that what is your expected output,

Regards,

Lin

[Anonymous]

Hi @v-lili6-msft 

Actually, my most important need is to be able to use the users participating in the survey that I chose on the first SurveyName slicer in other slicers.

That's why I first find the number of common users who participated in the survey I wanted in the first SurveyName Slicer. Later, I try to find the distribution of these users according to the answers they gave to the questions in the questionnaires I selected in the SurveyName2 Slicer.

It seems to me that if we can focus primarily on this part of the report, we will be able to solve the other one more quickly.

Best,

[v-lili6-msft]

hi  @Anonymous 

If so, SurveyName1 and SurveyName2 should be two different slicer and SurveyName1 must from a separate table.

Try to create a separate SurveyName table as below:

Separate SurveyName = VALUES('Responses Table'[SurveyName])

then use it as SurveyName1 slicer

Now add a conditional in the formula as below:

Measure 2 =

var SurrveryName1 = CALCULATETABLE(VALUES('Responses Table'[UserID]),FILTER('Responses Table','Responses Table'[SurveyName] in VALUES('Separate SurveyName'[SurveyName1])))

return

CALCULATE(COUNTA('Responses Table'[UserID]),FILTER('Responses Table','Responses Table'[UserID] in SurrveryName1))

Regards,

Lin

[Anonymous]

Hi @v-lili6-msft 

It's not true. When I select survey1 and survey2 in SurveyName1 filter it shows me 8 but It must be shown 3. (ABC1, ABC3, ABC4)

Okay we use only one SurveyName Slicer. And firstly we try to find common users in selected Surveys.

I have found it in this Measurement:

Count of Cohort = 
VAR tmpTable1 = GENERATE(VALUES(Responses[UserID]),
                         EXCEPT(
                                VALUES(Responses[SurveyName]),
                                CALCULATETABLE(VALUES(Responses[SurveyName]))))
VAR tmpTable2 = SUMMARIZE(tmpTable1,Responses[UserID])
VAR tmpTable3 = EXCEPT(VALUES(Responses[UserID]),tmpTable2)
RETURN COUNTROWS(tmpTable3)

But When I create a matrix table by SurveyName and AnswerText It shows to me wrong data:

[Anonymous]

Hey, Do you have any solution about my problem? Nobody can fix this?