Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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?
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.
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.
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!
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 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?
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 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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
83 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |