Forum Discussion
Visualizing multiple true/false questions in ONE stacked chart
Hi everybody
I'm new to Power BI and need some help visualizing multiple true/false questions in one chart as a percentage average of the sum sales prize. Here it goes:
| Sales opportunity, unique# | Sales prize | Is the car blue? | Is the car a 4WD | Does the car have backseats? |
| 11 | $ 100 | True | False | True |
| 12 | $ 300 | False | False | True |
| 13 | $ 250 | True | True | False |
| 14 | $ 175 | True | True | False |
| 15 | $ 100 | Fales | False | False |
Is it possible to display/visualize the true/false data in ONE stacked bar chart, where you get a percentage view.
Example:
Sum sales prize is: $925.
Percentage of cars that are blue: 60 %. Display: 60 % of sum sales price =$555
Percentage of cars that are 4WD: 40 %. Display: 40 % of sum sales price =$370
And so on... All in the same stacked bar chart.
I hope you're able to help.Thanks
BR
Anders
Anonymous
In this scenario, you can first transform your table in Query Editor and then you can get the expected result with one measure. Please refer to following steps.
- In Query Editor, duplicate Table1 and rename it to Table2.
- Delete the three columns (blue, 4WD and backseats) of Table1. Delete one column (Sales prize) of Table2.
- Multiple select the three columns (blue, 4WD and backseats) of Table2, click “Unpivot Columns”.
- Filter the rows which “Value” = True in Table2. Then delete the “Value” column.
- Select the “Attribute” column in Table2, click “Split Column by Delimiter” of Space at the right-most delimiter as below. Replace ? with null in “Attribute.2” column. Rename “Attribute.2” to “Type” and delete “Attribute.1” column.
- Close and apply Query Editor. A 1:* relationship between Table1 and Table2 will be created automatically. Create a measure with following formula. Set its Format to “Percentage”.
Percent = DIVIDE ( SUM ( Table1[Sales prize] ), CALCULATE ( SUM ( Table1[Sales prize] ), ALL ( Table2 ) ) ) - Drag Stacked bar chart into your canvas. Select Type for Axis, Percent for Value, Sales prize (Quick Calc Sum) for Tooltips.
Anonymous
After you update the data tables, you only need to click “Refresh All” in Query Editor.
Best Regards,
Herbert
5 Replies
Anonymous
In this scenario, you can first transform your table in Query Editor and then you can get the expected result with one measure. Please refer to following steps.
- In Query Editor, duplicate Table1 and rename it to Table2.
- Delete the three columns (blue, 4WD and backseats) of Table1. Delete one column (Sales prize) of Table2.
- Multiple select the three columns (blue, 4WD and backseats) of Table2, click “Unpivot Columns”.
- Filter the rows which “Value” = True in Table2. Then delete the “Value” column.
- Select the “Attribute” column in Table2, click “Split Column by Delimiter” of Space at the right-most delimiter as below. Replace ? with null in “Attribute.2” column. Rename “Attribute.2” to “Type” and delete “Attribute.1” column.
- Close and apply Query Editor. A 1:* relationship between Table1 and Table2 will be created automatically. Create a measure with following formula. Set its Format to “Percentage”.
Percent = DIVIDE ( SUM ( Table1[Sales prize] ), CALCULATE ( SUM ( Table1[Sales prize] ), ALL ( Table2 ) ) ) - Drag Stacked bar chart into your canvas. Select Type for Axis, Percent for Value, Sales prize (Quick Calc Sum) for Tooltips.
Thaks a lot! Just what I neeeded - quite a workaround though :)
Seems like I'm going to do a lot of "text-work" in the Query Editor, since the real table of course has a lot more content/questions. Do you know if the Query Editor is able to "remember" the copying of tables if I need to update theese numbers each month?
Thanks a lot, just what I needed! Seems like I have a lot "text editing" to do in the Query Editor, before I have the desired result.
Do you know if the table copying in the Query Editor is "remembered" or do I have to do this manually each time I update the data tables?
Anonymous
After you update the data tables, you only need to click “Refresh All” in Query Editor.
Best Regards,
Herbert