Forum Discussion
editing columns for a line chart
Hi everyone, help is needed 😞
I need to create a line chart that shows time vs depth for a digging/drilling process.
All I have now is follwing columns:
- step No: 1,2,3,...
- time in hrs: time it takes to drill the hole from one depth to another
- depth from: starting depth
- depth to: end depth
- activity: type of activity, where drilling is the main activity and thes rest is secondary (like replacing tools, waiting on weather).
Left is my current table. Right is how it should look like, with the line chart.
Cells marked by yellow indicate secondary, non-drilling activities (replacing and waiting on weather) and should be shown on the line chart as flat lines since the depth doesn't change.
The actual table contains many more rows, so not possible to edit it manually.
Should I start with two measures?
Thanks in advance!
7 Replies
- AllisonKennedy
Community Champion
I would try creating a Cumulative total based on Step No and plot Depth To vs Running Total Time (doesn't look like you're using/need Depth From, but if you are please explain how to handle the inconsistencies in steps 6 and 7)
See this post for reference and refer to Customer Class example: https://www.sqlbi.com/articles/computing-running-totals-in-dax/AllisonKennedy Hi Alison, thanks for your promt comment. I've added some annotations to the picture.
- AllisonKennedy
Anonymous Thanks, that's exactly what I thought. You'll need to add that first row in there as a data point, but otherwise focus on the blue and red boxes in your left picture.
AS CALCULATED COLUMN:
TotalTime = CALCULATE(SUM(Table[Time]), Table[Step]<=EARLIER(Table[Step]))Then graph that with [Depth to, m] column.For the green boxes, it seems like this data point is cheating in a way, as looking at the left table there's no assurance that the activity was actually drilling or that it started at 0? Where does this information come from? With Power BI it all needs to systematically connect and talk to each other, being pulled from somewhere.
