Calculating percentages of individual answers instead of grand total

[Tijevancasteren]

Hi all,

I'm currently analyzing a database with surveys for my graduation project. I'm stuck with the following problem; I have a multiple response answer which asks the respondents if they know a company or not. I unpivoted the columns and have the following bar chart at the moment. 

I want to transform this into a bar chart where I can see how many of the respondents ticked each answer. I have 628 respondents, so the first answer should reach around 95%. How can I calculate this? Thanks in advance!

[Eric_Zhang · Accepted solution]

@Tijevancasteren

Try to replace "COUNT" with "COUNTA".

[Eric_Zhang]

@Tijevancasteren

What is the value filed of the column chart? Is 628 the total survey amount?

Try to create a measure and put the measure to the value field.

percentages =
DIVIDE (
    COUNT ( yourTable[unpivotColumn] ),
    CALCULATE ( DISTINCTCOUNT ( yourTable[surveryID] ), ALLSELECTED ( yourTable ) )
)

If it is not your case, please post more details about your dataset.

[Tijevancasteren]

@Eric_Zhang

Thanks for you reply. I tried your measure, but theres an error in displaying the data, so I figured im doing something wrong.  628 is indeed the total amount of respondents/surveys. My table looks like this.

The first column has the respondent ID, the second is the unpivoted column, and the 3rd column is filled with 1's. My measure was like this:

Percentages =
DIVIDE (
COUNT ( 'Bekendheid (2)'[BekendheidOndernemingen]);
CALCULATE ( DISTINCTCOUNT ( 'Bekendheid (2)'[Respondentnummer] ); ALLSELECTED ( 'Bekendheid (2)' ) )
)

When I click details it shows me this:

Error Message:

MdxScript(Model) (3, 13) Calculation error in measure 'Bekendheid (2)'[percentages]: The function COUNT takes an argument that evaluates to numbers or dates and cannot work with values of type String.

I think it means the 2nd column with the company names, but that is indeed a text string!

[Eric_Zhang · Accepted solution]

@Tijevancasteren

Try to replace "COUNT" with "COUNTA".

[Tijevancasteren]

Thanks! This worked for that table. I tried to use this solution on another table. I have a table with two stacks, one for the rating of a subject, and one for the importance of a subject. The measures in the table on the right of the picture are correct.

When I put them together in 1 table, as shown on the left, the bottom right value has changed. What am I missing?

[Eric_Zhang · Accepted solution]

---

@Tijevancasteren wrote:

Thanks! This worked for that table. I tried to use this solution on another table. I have a table with two stacks, one for the rating of a subject, and one for the importance of a subject. The measures in the table on the right of the picture are correct.

When I put them together in 1 table, as shown on the left, the bottom right value has changed. What am I missing?

---

@Tijevancasteren

Thanks for sharing the pbix. May I know what you'd like to show in the left visual? I see there's two measures "Percentagesbelangveiligheid" and "PercentageBRDVeiligheid" in the values fields, both will be evaluated according to the column "Hoe zou u de onderstaande aspecten in het gebied Stappegoor beoordelen? (1 = zeer slecht, 5 = zee...-Veiligheid", and I think those measure are just working as they shall.

[Tijevancasteren]

@Eric_ZhangThanks, it seems you are right and I overlooked it somehow!

[Eric_Zhang]

---

@Tijevancasteren wrote:

Thanks! This worked for that table. I tried to use this solution on another table. I have a table with two stacks, one for the rating of a subject, and one for the importance of a subject. The measures in the table on the right of the picture are correct.

When I put them together in 1 table, as shown on the left, the bottom right value has changed. What am I missing?

---

I can't get the reason from the snapshot, would you mind sharing the pbix file? You can upload it to a network drive and share me the link, do mask sensitive data before sharing.