Sum by Department

[rlanger]

I have the following data and I need to display the sum of the ammount paid by department in order to calculate the % every employee represents.

My problem is that I don't want to show the Department.

Department	Employee	Amount Paid
Enginnering	Pedro	23.214,15
Enginnering	João Miguel	8.431,04
Enginnering	Francisco	6.352,07
Enginnering	Rodrigo	17.992,42
Sales	Alexander	0,00
Sales	Murilo	1.004,89
Sales	Joaquim	11.017,41
Sales	Fernando	0,00
Sales	Marcelo	1.197,05
Sales	César	998,66
Sales	João	16.550,91
Sales	Gustavo	7.380,69
Sales	David	0,00
Sales	Eduardo	2.893,43

The best I could get is this, but I can't get the sum on every line.

Employee	Amount Paid	Aggr(sum(Amount_Paid),Department)
Pedro	23.214,15	-
João Miguel	8.431,04	55.989,68
Francisco	6.352,07	-
Rodrigo	17.992,42	-
Alexander	0,00	-
Murilo	1.004,89	-
Joaquim	11.017,41	-
Fernando	0,00	-
Marcelo	1.197,05	-
César	998,66	-
João	16.550,91	-
Gustavo	7.380,69	-
David	0,00	41.043,04
Eduardo	2.893,43	-

[Vegar · Accepted solution]

Try adding a NODISTINCT to your aggr(), like this:

Aggr( NODISTINCT sum(Amount_Paid),Department)

[stevedark · Accepted solution]

Hi @rlanger 

If you did have the department in the table then it would be a simple expression:

sum(TOTAL <Department> [Amount Paid])

However, I can't find a simple way of hiding the extra column. You could use an autonumber in the load, and then have a column which just has values of 1, 2, 3 etc. for each department, so:

LOAD
  Department,
  AutoNumber(Department) as DeptNo,

Then use DeptNo as the dimension and replace Department with DeptNo in the expression above.

Another approach is to calculate the Department totals in the load script and then have that in a separate table, like this:

Data:
LOAD
    *
INLINE [
Department,Employee,Amount Paid
Enginnering,Pedro,23214.15
Enginnering,João Miguel,8431.04
Enginnering,Francisco,6352.07
Enginnering,Rodrigo,17992.42
Sales,Alexander,0.00
Sales,Murilo,1004.89
Sales,Joaquim,11017.41
Sales,Fernando,0.00
Sales,Marcelo,1197.05
Sales,César,998.66
Sales,João,16550.91
Sales,Gustavo,7380.69
Sales,David,0.00
Sales,Eduardo,2893.43
];

DepTotal:
LOAD
    Department,
    sum([Amount Paid]) as [Department Paid]
RESIDENT Data
GROUP BY Department;

You can then simply do sum([Department Paid]) to get the department totals.

The big advantage of this approach is that if you make a selection on an indivdual then you will still be able to see their percentage correctly:

Hopefully that gives you what you need to get to the result that you are trying to achieve.

I've attached the app that I used to test.

Good luck!

Steve

[rlanger]

Thanks Vegar! It worked!

One important thing I learned is that your data needs to be perfect.

For example: I had the same Employee with 2 Departments, including null sometimes.

With this problems the formula would bring Null in this cases.

[rlanger]

Thanks stevedarjk!

It worked too!