Line Chart Not Charting Correct Data

Kendra Michaels, CAPM

I'm having trouble getting a line chart to chart the appropriate data. I am tracking KPIs for my sales team and collecting in a source sheet. I then have a report for each sales person that pulls their data from that sheet. I have made each of them a dashboard so they can see their progress and I want to charge their data over time on the dashboard. We have the KPI data going back multiple years, but I'd like to just chart this year to date and last year to date laid over eachother.

What I ultimately want is one chart with 2 lines: a line for 2018 and a line for 2019. X axis should be Week # of the year and Y axis should be the metric (count of something or $ of something). I've included screenshots of everything I have.

How do I set this all up so it will chart what I want? Or, what's the closest I can get to that?

 

Paul Newcome

You would need to have 2018 in one row and 2019 in another row. Have your week numbers going across the columns with the week numbers repeated in the first row.

 

You would then use SUMIFS functions to total everything up into a table.

 

The table would look something like this (column headers in bold):

.

Table Year              Week 1          Week 2           Week 3              Week 4

                                   1                    2                     3                          4

2018                          f

2019

.

Where you see the "f", you would put a formula similar to this...

 

=SUMIFS([Column To Sum]:[Column To Sum], [Year Column]:[Year Column], $[Table Year]@row, [Week Number Column]:[Week Number Column], [Week 1]$1)

.

Note the use of the $ to lock in certain column and row references. This allows you to dragfill the formula down into the 2019 row then continue to dragfill both rows over for the remainder of the table.

.

You would then use this table in your Chart widget on the Dashboard.