Forum Discussion
Overlay/Combine Line Charts
I am trying to achieve a visualization of multiple lines graph to show:
- salesman's monthly average selling rate (ASR)
- company's overall monthly average selling rate
Objective is to compare the performance of the salesman vs company's average.
My dataset are in this order
Transaction Month ASR SalesmanName
Sales001 JAN18 $100 AA
Sales002 JAN18 $90 BB
Sales003 FEB18 $105 AA
Sales004 FEB18 $99 AA
Sales005 FEB18 $120 BB
Sales006 MAR18 $100 AA
Sales007 MAR18 $80 BB
Sales008 MAR18 $80 BB
Sales009 APR18 $90 AA
So after calculation with tabulation, I want to present in line chart
Month JAN18 FEB18 MAR18 APR18
Company $95 $108 $86.67 $90 (simple averaging calculation, eg. JAN18 is $100+$90/2)
AA $100 $102 $100 $90
BB $90 $120 $80
So I have created 2 line charts (figures in picture are for reference only):
1-->(the one with fine line) Axis=Month; Values=ASR; Legend=SalesmanName
2-->(the one with green dotted line) Axis=Month; Values=ASR; Legend=NONE
I think the source of my problem is here, I am trying to display the same measure twice.
However, when I carefully put the 2 together, I would get this nice chart for comparison between the company average(green dotted) and the salesman's (grey) trend line performance:
But, in the case of Salesman BB where he has no sales number in APR18, the graph will resize and both charts will not overlay properly. See the months are not overlaying accurately on top of each other.
However, I currently had a dumb solution to it by selecting more than 1 salesman where the other supporting salesman has the full data every month
Can anybody please help to give any solution to use only 1 graph instead of overlaying 2 graphs? How should I manipulate my dataset?
Hi gelseytanse,
I would suggest you add them all in one visual. Please check out the demo in the attachment.
AA = CALCULATE ( AVERAGE ( Table1[ASR] ), 'Table1'[SalesmanName] = "AA" )
BB = CALCULATE ( AVERAGE ( Table1[ASR] ), Table1[SalesmanName] = "BB" )
Company = AVERAGE(Table1[ASR])
Best Regards,
Dale
3 Replies
- v-jiascu-msftMicrosoft Employee
Hi gelseytanse,
I would suggest you add them all in one visual. Please check out the demo in the attachment.
AA = CALCULATE ( AVERAGE ( Table1[ASR] ), 'Table1'[SalesmanName] = "AA" )
BB = CALCULATE ( AVERAGE ( Table1[ASR] ), Table1[SalesmanName] = "BB" )
Company = AVERAGE(Table1[ASR])
Best Regards,
Dale
- v-jiascu-msftMicrosoft Employee
- gelseytanseFrequent Visitor
Hi Dale,
Thanks for the solution!
Gelsey
