I believe the attached is what you want. You also need to add Month with the Dept_id in the expression.
Test.qvw 150.8 K
It seems like you have a lot of things dependent on this. I suggest calculating it in the script:
LOAD * INLINE [
EMP_id, Dept_id, values,Month
LEFT JOIN (Data)
sum(values) as [SumOfDept]
GROUP BY Dept_id, Month;
I have also attached a .qvw with this in it.
Test.qvw 152.2 K
Nicole, i do appreciate your time
I do understand that this can be done at the Sciprt level but it will be a static for dimensions ( suddenly if i want to add another level like Region) need to change the script.
I just gave the Sum as example for sample dataset. We need to do the Average, Sum, Median besides there are 10-12 different measures which needs these analysis- trying to see if we can accomplish this at the report level.