Forum Discussion
Visualize on chart based on various columns
I have this dataset:
| Business_id | KPI_1 | KPI_2 |
| 000001 | 0 | 1 |
| 000002 | 1 | 0 |
| 000003 | 1 | 0 |
| 000004 | 0 | 0 |
| 000005 | 0 | 0 |
| 000006 | 0 | 1 |
| 000007 | 1 | 1 |
| 000008 | 0 | 0 |
| 000009 | 1 | 0 |
| 000010 | 1 | 0 |
| 000011 | 0 | 1 |
| 000012 | 0 | 0 |
| 000013 | 1 | 0 |
| 000014 | 0 | 0 |
| 000015 | 1 | 1 |
| 000016 | 0 | 0 |
| 000017 | 1 | 1 |
| 000018 | 1 | 0 |
| 000019 | 0 | 1 |
| 000020 | 0 | 0 |
and I would like to visualize it on a chart that the X axis will be the column "KPI 1" and "KPI 2" and so on and Y axis will be the volume (count) of the related KPI, something like this:
What is the best way to do it?
Hi Anonymous ,
Here are the steps you can follow：
1. Create calculated table.
Table 2 = UNION( SELECTCOLUMNS( 'Table',"Business_id",[Business_id],"KPI","kpI_1","Value",[KPI_1]), SELECTCOLUMNS( 'Table',"Business_id",[Business_id],"KPI","kpI_2","Value",[KPI_2]))2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
4 Replies
Hi Anonymous ,
Here are the steps you can follow：
1. Create calculated table.
Table 2 = UNION( SELECTCOLUMNS( 'Table',"Business_id",[Business_id],"KPI","kpI_1","Value",[KPI_1]), SELECTCOLUMNS( 'Table',"Business_id",[Business_id],"KPI","kpI_2","Value",[KPI_2]))2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi Anonymous
One approach to achive this is by
1) Transform your table by unpivotting the columns to rows
2) Use a regular Column chart visual to obtain the desired visual
The transformation can be achived by following the example here: Unpiot columns
Unpivot so that you have three columns:
- Business_id
- Attribute (rename to KPI)
- Value
, as illustrated below.Based on the transformed table, you can obtain the desired visual by using a column visual.
Set
y-axis= Values
x-acis= Attribute (or KPI if you have renamed it)
Hope this helps.
Kind regards!
Consider accepting proposed answer as solution if it solved your problem.Ok. Im not sure how to solve your issue without the use of unpivotting.
(note I am not an expert!)
Here is a similar question marked as solved:
Combine multiple measures in single bar chart
using either
- Custom visual "Side by Side Bar Chart(Standard)" from PBIVizEdit.com
- 2 Measures + Field parameter
Hope the linked post or other user helps you solve the problem.
Kind regards