Overlay/Combine Line Charts

[gelseytanse]

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?

[v-jiascu-msft]

Hi @gelseytanse,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[gelseytanse]

Hi Dale,

Thanks for the solution! 

Gelsey

[v-jiascu-msft · Accepted solution]

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