Aggregate then Count with Condition - How to do this?

Fri, 23 May 2014 01:53:54 GMT

Hi,

I have two input tables, as below:

I would like to achieve a report table with Month-to-Date performance like this:

Month-to-Date
Region	SalesPerson Count (with >=20 sales in MTD total)	Target	% Achieve
R1	1	2	50%
R2	1	3	33%
R3	0	3	0%
R4	2	4	50%

What formula can I use for SalesPerson Count in order to achieve this?

Thanks in advance.

Re: Aggregate then Count with Condition - How to do this?

SergeyMak — Fri, 23 May 2014 02:18:21 GMT

It depends on your data model. But anyway you need additional Calendar table.

If you load everything into one table you just divide Sum({$<Type={"Actual"}>}Amount)/Sum({$<Type={"Budget"}>}Amount).

If you'd like to have these data in separate table, then you need to create linktable and connect your tables by synthetic key Region&Date AS %KEY

Then you just divide Sum(Sales)/Sum(Target)