Average sales monthly sales in a year per salesman

[#1]

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.

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?

[swuehl]

Try maybe

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

And why do you divide by 5?

[MarcoWedel · Accepted solution]

Sum( {1<Year={2014}>} Total [Amount])/12/Count({1<Year={2014}>} Distinct Salesman)

Sum( {1<Rok={2014}>} [Wartość netto])/Count({1<Year={2014}>} Distinct Salesman)

[#4]

I'm dividing by 5 because I have 5 salesman.

[#5]

Works perfect. Thank you very much!

You saved me a lot of time and trouble.

[MarcoWedel]

You're welcome

Regards

Marco