Visualizing multiple true/false questions in ONE stacked chart

[Anonymous]

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

[v-haibl-msft · Accepted solution]

@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.

1. In Query Editor, duplicate Table1 and rename it to Table2.
2. Delete the three columns (blue, 4WD and backseats) of Table1. Delete one column (Sales prize) of Table2.
   
3. Multiple select the three columns (blue, 4WD and backseats) of Table2, click “Unpivot Columns”.
4. Filter the rows which “Value” = True in Table2. Then delete the “Value” column.
   
5. 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.
   
   
6. 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 ) )
   )

7. Drag Stacked bar chart into your canvas. Select Type for Axis, Percent for Value, Sales prize (Quick Calc Sum) for Tooltips.
   

[v-haibl-msft · Accepted solution]

@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.

1. In Query Editor, duplicate Table1 and rename it to Table2.
2. Delete the three columns (blue, 4WD and backseats) of Table1. Delete one column (Sales prize) of Table2.
   
3. Multiple select the three columns (blue, 4WD and backseats) of Table2, click “Unpivot Columns”.
4. Filter the rows which “Value” = True in Table2. Then delete the “Value” column.
   
5. 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.
   
   
6. 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 ) )
   )

7. Drag Stacked bar chart into your canvas. Select Type for Axis, Percent for Value, Sales prize (Quick Calc Sum) for Tooltips.
   

[Anonymous]

@v-haibl-msft

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?

[v-haibl-msft · Accepted solution]

@Anonymous

After you update the data tables, you only need to click “Refresh All” in Query Editor.

Best Regards,

Herbert

[Anonymous]

@v-haibl-msft

I will test to see if it works out. Thank you.

[Anonymous]

@v-haibl-msft

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?