Stacked Bar Chart to show Trend line

[rachaelwalker]

I have a bar chart that shows % of tasks delivered on time (very bottom visual). I need to add a trend line to give a better visualization of the trend. I have tried to use the line and stacked bar chart visual but I cannot get it to show the data in %. Any help or suggestions are appreciated. thank you.

Delivered on time formula is below

Delivered On Time = IF('Project Install Schedule'[MIN Scheduled End] < 'Project Install Schedule'[MAX Entered Time], "No", "Yes")

 

[Whitewater100 · Accepted solution]

Hi:

I had a little luck changing the chart type to Line & Clustered Column and using DATE in Axis. Then you get a trend line. There are perhaps more ways but this got closer to what you'd like.

[Whitewater100]

Hello: I did my best with the data available. I made two sets for percent and number of projects compeleted  and you can swop out. I did not find trend on stacked column but created average monthly measure to substitute. I hope this helps!

https://drive.google.com/file/d/1bpN50xLPNM4Ct10eX2Tk5Ewa49sIJ5D0/view?usp=sharing 

[Whitewater100 · Accepted solution]

Hi:

I had a little luck changing the chart type to Line & Clustered Column and using DATE in Axis. Then you get a trend line. There are perhaps more ways but this got closer to what you'd like.

[rachaelwalker]

@Whitewater100 the line and cluster does not show the data values as a %. I can change it to show data as a percentage of the whole, but then its not correct for each month. I accidentally marked your response as the solution. 

[Whitewater100]

Hi Rachael:

Would you happen to have any data you can share? Or perhaps tell me what formula you want to show % of total? I think if you get a measure for this it will be fine. Anyways whatever you can share, plus what an expected result would be will help me understand - I will look at it and reply.

Thanks

[rachaelwalker]

I will include some of the data below. I basically want it to look just like what I have but a line to emphasize a trend line. Image below to give an idea. So I imagine I will need to use the line and stacked visual but I cannot get it to show in % for that month. My formula is in the original post. It is a calculated column. for the visual below Green = "yes" and red = "no". I appreciate you taking the time to help me. This seems like an easy fix so its driving me crazy. 

Delivered On Time	Date	Project Number
Yes	9/29/2020 0:00	4041
Yes	10/2/2020 0:00	4032
No	10/6/2020 0:00	4033
Yes	10/6/2020 0:00	4038
No	10/7/2020 0:00	4029
No	10/16/2020 0:00	8058
Yes	10/20/2020 0:00	4036
No	10/21/2020 0:00	4037
No	10/29/2020 0:00	4054
Yes	10/30/2020 0:00	4058
Yes	11/6/2020 0:00	12153
Yes	11/13/2020 0:00	4052
Yes	11/19/2020 0:00	4050
Yes	11/20/2020 0:00	4053
No	11/25/2020 0:00	4045
Yes	12/2/2020 0:00	4083
Yes	12/3/2020 0:00	4065
No	12/4/2020 0:00	4035
Yes	12/10/2020 0:00	12187
Yes	12/18/2020 0:00	4066
No	12/21/2020 0:00	4048
No	12/23/2020 0:00	8159
Yes	12/23/2020 0:00	4085
Yes	12/29/2020 0:00	4063
No	12/31/2020 0:00	4126
Yes	12/31/2020 0:00	8178
No	1/5/2021 0:00	4076
Yes	1/5/2021 0:00	4113
No	1/7/2021 0:00	8196
Yes	1/7/2021 0:00	4051
No	1/8/2021 0:00	4069
Yes	1/8/2021 0:00	8165
Yes	1/12/2021 0:00	4128
Yes	1/13/2021 0:00	4056
Yes	1/14/2021 0:00	4137
No	1/15/2021 0:00	4055
Yes	1/15/2021 0:00	4102
No	1/20/2021 0:00	4117
Yes	1/20/2021 0:00	4145
No	1/21/2021 0:00	8236
No	1/27/2021 0:00	4047
Yes	1/28/2021 0:00	8210
No	1/29/2021 0:00	4132
Yes	2/4/2021 0:00	4127
No	2/5/2021 0:00	4109
Yes	2/5/2021 0:00	8198
No	2/9/2021 0:00	4122
No	2/11/2021 0:00	8229
Yes	2/11/2021 0:00	4168
Yes	2/12/2021 0:00	4094
No	2/16/2021 0:00	4120
No	2/18/2021 0:00	4138
Yes	2/18/2021 0:00	4106
No	2/19/2021 0:00	8083
Yes	2/19/2021 0:00	8246
No	2/23/2021 0:00	4167
Yes	2/24/2021 0:00	4100
Yes	2/26/2021 0:00	8254
No	3/3/2021 0:00	4130
Yes	3/3/2021 0:00	4124
No	3/4/2021 0:00	4160
Yes	3/4/2021 0:00	4135
Yes	3/5/2021 0:00	12262
Yes	3/9/2021 0:00	4060
Yes	3/11/2021 0:00	4140
No	3/12/2021 0:00	8243
Yes	3/15/2021 0:00	4171
Yes	3/17/2021 0:00	4097
No	3/18/2021 0:00	8282
Yes	3/18/2021 0:00	4166
Yes	3/19/2021 0:00	4109
Yes	3/23/2021 0:00	4139
No	3/25/2021 0:00	4074
No	3/26/2021 0:00	4125
Yes	3/26/2021 0:00	8243
Yes	3/30/2021 0:00	4187
No	3/31/2021 0:00	4104
Yes	3/31/2021 0:00	4179
Yes	4/1/2021 0:00	4212
No	4/2/2021 0:00	4110
No	4/7/2021 0:00	8325
Yes	4/7/2021 0:00	4210
Yes	4/8/2021 0:00	4209
No	4/9/2021 0:00	4143
Yes	4/9/2021 0:00	12463
Yes	4/13/2021 0:00	4105
Yes	4/14/2021 0:00	4191
No	4/16/2021 0:00	4116
Yes	4/16/2021 0:00	4195
Yes	4/20/2021 0:00	4131
No	4/22/2021 0:00	8252
No	4/23/2021 0:00	8364
Yes	4/23/2021 0:00	12392
No	4/26/2021 0:00	4204
No	4/28/2021 0:00	4199
No	4/30/2021 0:00	4165
No	5/1/2021 0:00	4180
No	5/5/2021 0:00	4068
Yes	5/5/2021 0:00	4221
No	5/6/2021 0:00	4237
No	5/7/2021 0:00	4181
No	5/11/2021 0:00	4207
No	5/13/2021 0:00	4194
Yes	5/14/2021 0:00	4246
No	5/15/2021 0:00	4146
Yes	5/17/2021 0:00	4230
No	5/18/2021 0:00	4220
No	5/20/2021 0:00	4219
Yes	5/20/2021 0:00	4040
No	5/21/2021 0:00	4222
No	5/24/2021 0:00	4198
Yes	5/26/2021 0:00	4253
No	5/27/2021 0:00	4197
Yes	5/28/2021 0:00	8486
Yes	6/2/2021 0:00	12351
No	6/3/2021 0:00	4233
Yes	6/4/2021 0:00	4201
No	6/7/2021 0:00	8191
Yes	6/9/2021 0:00	4214
No	6/10/2021 0:00	4244
Yes	6/11/2021 0:00	12800
No	6/14/2021 0:00	4183
No	6/16/2021 0:00	4184
Yes	6/16/2021 0:00	4243
No	6/17/2021 0:00	4234
No	6/18/2021 0:00	4189
Yes	6/18/2021 0:00	4264
Yes	6/22/2021 0:00	8602
No	6/27/2021 0:00	4202
No	7/1/2021 0:00	4241
Yes	7/1/2021 0:00	4284
No	7/2/2021 0:00	4086
No	7/3/2021 0:00	4153
Yes	7/7/2021 0:00	8629
No	7/8/2021 0:00	8467
Yes	7/8/2021 0:00	8564
No	7/9/2021 0:00	4231
Yes	7/9/2021 0:00	4320
No	7/13/2021 0:00	4213
Yes	7/13/2021 0:00	4312
Yes	7/14/2021 0:00	4305
Yes	7/15/2021 0:00	12700
Yes	7/16/2021 0:00	4255
Yes	7/21/2021 0:00	12888
Yes	7/22/2021 0:00	4339
Yes	7/27/2021 0:00	4306
Yes	7/29/2021 0:00	4317
No	7/30/2021 0:00	8557
Yes	7/30/2021 0:00	4337
No	7/31/2021 0:00	4157
No	8/2/2021 0:00	4300
Yes	8/3/2021 0:00	4086
No	8/6/2021 0:00	8619
No	8/7/2021 0:00	4271
Yes	8/10/2021 0:00	4302
No	8/11/2021 0:00	4163
Yes	8/12/2021 0:00	4342
No	8/13/2021 0:00	4290
Yes	8/16/2021 0:00	4416
Yes	8/17/2021 0:00	4157
No	8/19/2021 0:00	4303
Yes	8/19/2021 0:00	12875
No	8/20/2021 0:00	8634
Yes	8/20/2021 0:00	8749
Yes	8/25/2021 0:00	4245
No	8/27/2021 0:00	4286
Yes	8/27/2021 0:00	8692
Yes	8/30/2021 0:00	4419
No	9/1/2021 0:00	4386
Yes	9/1/2021 0:00	4364
Yes	9/2/2021 0:00	4263
No	9/3/2021 0:00	8659
No	9/8/2021 0:00	4333
No	9/10/2021 0:00	4278
Yes	9/10/2021 0:00	4401
No	9/16/2021 0:00	4292
No	9/17/2021 0:00	8709
Yes	9/17/2021 0:00	4409
Yes	9/20/2021 0:00	4476

[Whitewater100 · Accepted solution]

Maybe you will like moving average. Here is a 3-mnoth and it smooths the average out and is a trend lagging indicator. the link from before should have it.

[rachaelwalker]

This is exactly what I was looking for. Thank you so much Bill!

[Whitewater100]

Hi Rachael:

I will check it out now. Bill