5 Replies Latest reply: Dec 8, 2014 4:43 AM by Marco Wedel RSS

    Average sales monthly sales in a year per salesman

      Hello,

      I'm preparing a chart with monthly sales per salesman for one desired year.

      I have a xls table with invoice lists from 2013 and 2014 like this:

      Date of sales | Salesman | Amount |

      2014-01-05   | 1               | 500 |

      2013-06-09   | 3               | 600 |

      2013-10-12   | 1               | 300 |

      2013-10-23   | 6              | 200 |

      2014-01-21   | 3              | 950 |

      2013-06-29   | 1              | 720 |

      2013-10-24   | 3              | 100 |

      2014-12-21   | 4              | 280 |

      2012-02-06   | 7              | 900 |

      2013-07-19   | 6              | 400 |

       

      Real file has over 9 000 lines.

       

      I want to prepare combo chart with sales per month for 2014 year with Filter Pane to filter sales per salesman (which is easy).

      Hardest part for me is to put a line on chart with average sales per month per salesman (2014 sales/12 months/Number of salesman) which disregards any selections of Filter Pane.

      I want to put another line with average sales per salesman for each month.

      It should look like this.

      Bez tytułu.jpg

       

      I tried many different versions but when I select salesman number on Filter Pane it changes average lines also.

      I use this expression in measurments:

      Sum({1} {$<Year={2014}>} Total [Amount])/12/5  - for average monthly sales from whole year per salesman

      Sum({1} {<Rok={2014}>} [Wartość netto])/5  - for average monthly sales per salesman

       

      Any ideas?