The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
141 | |
109 | |
107 | |
76 | |
61 |
User | Count |
---|---|
276 | |
129 | |
124 | |
100 | |
89 |