Forum Discussion
How do i format my data to create a column chart?
Hi i Have the following data:
Im trying to present this for each date, but i struggle to find a visual, and how to select values for axis, legend and values.
What i want i a chart that:
x-axis: Each "ConnectionX" should have its own column
y-axis: Should show the value of the ConnectionX
There is already a slicer that sorts out the dates in the Dashboard, seems to be so easy like "Column chart" here:
https://www.pluralsight.com/guides/bar-and-column-charts-in-power-bi
But i cant seem to place the values in the correct boxes (axis/legend/value etc)
Here is how you can do it. First of all, unpivot the connection columns in Power Query to get:
Next create a Calendar Table and a Dimension table for Conecctions:
Create the relationships between the corresponding fields in the model
Create the measures for the visuals:
Sum Time = SUM('Table'[Time])Since you can't use a Time format in a bar chart, convert this sum into seconds
Time in seconds = HOUR([Sum Time]) * 3600 + MINUTE([Sum Time]) * 60 + SECOND([Sum Time])Now create the visual using the date field from the calendar table (make the axis categorical), the connection field from the dimension table for the legend and the measure(s). In the bar chart you can add the [Sum Time] as a tooltip.
I've attached the sample PBIX file
5 Replies
You need to unpivot all the Connection columns
Hm, maybe its a bad example, but does it mean that all values must be unique:
https://support.microsoft.com/en-us/office/unpivot-columns-power-query-0f7bad4b-9ea1-49c1-9d95-f588221c7098And will the data still be related to the actual date?
Hi again, so i test this.
My connection-columns, can range from 0-169, which means for one date i could get up to 169 duplicates.
Any other way, could i create a new table with the connections, and have a 1-1-realationShip back to the date?Here is how you can do it. First of all, unpivot the connection columns in Power Query to get:
Next create a Calendar Table and a Dimension table for Conecctions:
Create the relationships between the corresponding fields in the model
Create the measures for the visuals:
Sum Time = SUM('Table'[Time])Since you can't use a Time format in a bar chart, convert this sum into seconds
Time in seconds = HOUR([Sum Time]) * 3600 + MINUTE([Sum Time]) * 60 + SECOND([Sum Time])Now create the visual using the date field from the calendar table (make the axis categorical), the connection field from the dimension table for the legend and the measure(s). In the bar chart you can add the [Sum Time] as a tooltip.
I've attached the sample PBIX file
