I'm a little confused by your example. What is being multiplied? 10*20*30 / 30
I'm also a little confused - perhaps different numbers explain the calculation better.
Best guess... I think you are looking for something like
total (sum([Col A]) * sum([Col B]) ) / total( sum([Col B]) )
(window_sum(sum([Col A])) * window_sum(sum([Col B])) ) / ( window_sum(sum([Col B])) )
Hope that helps,
Thanks Dana and Jim for your reply.
Let me give more details on the issue I am facing with. I have a table something similar to given below.
For each and every dept, I will have one record with Name as NA which would hold value for Dept Wght column. I need to use that column value to multiply with Emp sal column for each and every record with same Dept.
Calc = sum(Emp Sal * Dept Wght)/ sum(Dept Wght)
Thus if I create a report at Dept level , it should give me for Dept A = 6000/10 = 600
Dept Wght A 60 B 60
Dept Name Emp Sal Dept wght A Hema 100 A John 200 A Kevin 300 A NA 10 B Mary 10 B Mae 20 B Frank 30 B NA 20
If I try to sum up dept wght value then multiply with Emp Sal column it does not give me the right figure. Any help appreciated.
Thanks for the explanation. I'm still a little confused and I think I'm missing part of the problem. I think Dept A in the output table above should be 600 to match the preceding text?
But the main confusion is that your formula
SUM(Emp Sal * Dept Wght)/SUM(Dept Wght)
== SUM(Emp Sal * Dept Wght)/Dept Wght (only one Dept Wght / dept)
== SUM(Emp Sal)
Yes you are right. It is 600.
I apologize for the typo error. To answer on your second question, denominator is the sum(dept wght).
If you could help me in achieving numerator that would be great.
SUM(Emp Sal * Dept Wght)/Dept Wght (only one Dept Wght / dept)
Given your example, SUM(Emp Sal * Dept Wght)/SUM(Dept Wght) is equivalent to SUM(Emp Sal). This will hold true as long as there is only one Dept Wght (x) value per department --- (100*x + 200*x + 300*x)/x == (100 + 200 + 300)*x / x = (100 + 200 + 300)
If you take your example data, open it in Tableau, double-click on Dept, double-click on Emp Sal. You'll get your example output -- sum of Emp Sal by department.
So I'm still confused by what you're trying to calculate.