Forum Discussion
Need you help with this chart
Hi All
how can I vesualize this data in power bi to have the same visual in the picture
| index | intention to return | possibility to return |
| 1 | Yes | No |
| 2 | Yes | Yes |
| 3 | No | No |
| 4 | No | No |
| 5 | Yes | Yes |
| 6 | No | Yes |
| 7 | No | Yes |
| 8 | No | No |
| 9 | No | No |
| 10 | Yes | No |
haniizz_derar20 try below
First you need to unpivot the last column as per below from power query.
-
there is two way
1. cluster bar chart create measure as per below
Measure =
VAR _A=
COUNT('Table'[index])
VAR _B =
CALCULATE ( COUNT('Table'[index]), ALLSELECTED('Table'[Value]) )
RETURN
DIVIDE ( _A, _B )then plot into chart
2.create stacked bar chart without create any measure direcltly plot in to chart as per below
You should unpivot the table first using Power Query.
Select the fields intention to return and possibility to return, right click on them and unpivot columns.
Then create a % share measure using the following formula:
% Share = VAR Volume = COUNT(Sheet1[index]) VAR AllVolume = CALCULATE ( COUNT(Sheet1[index]),ALLSELECTED(Sheet1) ) RETURN DIVIDE ( Volume, AllVolume )Finally create the graph.
PowerBI workspace attached
thank you ddpl for your reply
can I know the measure that you used in the previous example ?
and how can I get the same chart as in my question ?
thanks again
6 Replies
- ddpl
Solution Sage
haniizz_derar20 try below
First you need to unpivot the last column as per below from power query.
-
there is two way
1. cluster bar chart create measure as per below
Measure =
VAR _A=
COUNT('Table'[index])
VAR _B =
CALCULATE ( COUNT('Table'[index]), ALLSELECTED('Table'[Value]) )
RETURN
DIVIDE ( _A, _B )then plot into chart
2.create stacked bar chart without create any measure direcltly plot in to chart as per below
thank you ddpl for your reply
can I know the measure that you used in the previous example ?
and how can I get the same chart as in my question ?
thanks again
- themistoklis
Community Champion
You should unpivot the table first using Power Query.
Select the fields intention to return and possibility to return, right click on them and unpivot columns.
Then create a % share measure using the following formula:
% Share = VAR Volume = COUNT(Sheet1[index]) VAR AllVolume = CALCULATE ( COUNT(Sheet1[index]),ALLSELECTED(Sheet1) ) RETURN DIVIDE ( Volume, AllVolume )Finally create the graph.
PowerBI workspace attached
- TomasAndersson
Hi!
If you want to have a single visual that looks like that you'd have to restructure your data a bit:
In the model view, right click your data table, select Edit query and under "Transform" click "Unpivot columns" while having your two columns with questions selected:This will restructure the table (I have also changed the name of the columns here):
Now, you can create a measure that calculates the percentage Yes's and No's in a question.Percentage = DIVIDE( sum('Table'[index]) , //Number of answers of current type (Yes/No) in current question CALCULATE( sum('Table'[index]), ALLSELECTED('Table'[Answer]) //All answers in current question ) )Then you can create a visual that looks like what you're looking for:
There are other ways where you don't have to transform your data, but you need more/more complicated measures. So if you can restructure the data, it would be the easiest. You could even adjust the source data so it looks "right" from the start, so you don't need to do it in Power Query.
Hope this helps!
thank you all for your support , it was really helpfull
kind regards
- ddpl
