Show % of Total in bar chart

[jannahds]

Hi there, 

I'm hoping there is someone who can help me.  

I am analyzing survey data.

The first question asks if there's been a "change in size".  If they say "yes", then ask a follow up question that explains the reasons for the change in size.  This is a multiple-choice question and they can choose as many options as they want for explaining the change in size.

Now I want to show a bar chart that shows all the different reasons why there's a change in size, and the Value should be the % of the # of interviewees who mentioned this reason.

Because of the layout of the survey, if you added up all the percents across this bar chart, it should be more than 100%, because each interviewee is allowed to answer multiple choices.

But right now, this is all that I've been able to figure out:

The x-axis just shows the different reasons given.

The Value is a measure that I've currently got calculated as this:

%Reasons Change in Size = 
DIVIDE (
COUNTA('mc_Explain Change in Size'[_uuid]),
CALCULATE ( COUNTA('MSMA 2A - KI Goods (Main Table)'[_uuid]),ALL('MSMA 2A - KI Goods (Main Table)'[_uuid]))
)

So, "_uuid" in each case is the unique ID code for each interviewee.  If it's the _uuid from the main table - there is just one row for each interview.  But the _uuid in the "Explain Change in Size" table is repeated multiple times (one time for each different option they chose).  

So somehow, I just need the "count" of the UUID from the "Explain Change in Size" table to be filtered based on each option.  

I'm hoping someone can help!

Thanks!

Janna

[StefanoGrimaldi]

can you provide a example of it, maybe a excel file with some dummy data and a manual example of what you aiming for, to better respond the topic. 

[jannahds · Accepted solution]

Okay - got it - you were super close, and it was indeed very simple. 

Here's the bar chart in the end.  No measure used.  But I was counting the wrong _uuid in the original version.

So, if I just drop the _uuid in from the main table (the parent table, not the child table) - then it gives the correct visualisation I'm looking for.  

Thanks!

[aj1973]

Hi @jannahds 

Are you trying to calculate the number of rows by category or summing up some numbers? I think your measure is dividing the same number by itself. 

Try to put your 2 measures inside the DIVIDE in seperate once and then use them in a card to see if they are equal and from act upon the filters until you get the right one

A1 = COUNTA('mc_Explain Change in Size'[_uuid])
and

A2 = CALCULATE ( COUNTA('MSMA 2A - KI Goods (Main Table)'[_uuid]),ALL('MSMA 2A - KI Goods (Main Table)'[_uuid]))

see A1 and A2 how they are calculated in a Card visual.

[StefanoGrimaldi]

If I understand correctly it a simply soluction actually, you dont need a measure for this, 

create your visual with the X-Axis as the change motives as you have, them add that same field as value and click on the field setting for the visual and set it to count: 

then in the circle area click it and select show % of grand total

[jannahds]

Hi!  So, in your bar chart - the total of the percentages add up to 100% - because you're calculating the percentage of times each motivation showed up out of the total list of motivations.  However, what I'm trying to do is get it to be a percentage of the # of interviews (a much lower number).

So - the number of interviewees is 10, for example, and each interviewee gives 3 motivations for change - so I end up with 30 motivations total.

Now in the final bar chart, I'm trying to see what % of interviewees gave "A" as the reason for change - so maybe 80% of interviewees gave "A" as the reason, and only 10% gave "B" as the reason, and maybe 30% gave "C" as the reason...does that make sense?

Just not sure what the measure needs to be to calculate this...

Thanks!

Janna