I'm trying to create a measure which multiples the number of existing titles against a fee amount, and then divides this calculated amount against the number of rows of that particular title. Basically I'm trying to distribute the fee equally to each company holding that particular title, which I think is a weighted average.
For example, Company A would result as so:
Count(Title) * Fee
Count(A) * 900
1 * 900 = 900
900 / 3 = 300
Count(Title) * Fee
Count(B) * 1000
1 * 1000 = 1000
1000 / 2 = 500
So Company A's Total would be 800 instead of 1900.
I thought the correct expression would be this:
(Count(Title) * Fee) / Aggr(Count(Title),Title)
But it doesn't seem to be working for all rows and I'm stuck on what to do.
Thanks in advance to anyone who figures it out!
Hey guys sorry for opening this up again but I'm still having problems.
After trying out the solution I've discovered that it still doesn't work how I like it to, I don't think I did a good job of explaining (didn't include the Title_Type table) so I'll try again.
I have 2 tables:
Now for example if Title_Type AA has a fee of $900 and Title A has 3 companies sharing it, Company A should have 1/3 of that fee and have $300.
And then as Title_Type BB has a fee of $1000 and Title B has 2 companiies sharing it, Company A would have 1/2 of that fee and have $500.
The total then would be $800 for Company A.
The current formula which has been suggested:
Sum(Current_Fee) / Count(distinct Company)
Doesn't seem to be working for this. Instead it's summing up the fees of titles without repeating summing up the fees of recurring title_types.
What I need it to do is divide the fee for the title by the number of company's with that same title, and then add up all calculated fees.
Sorry if I haven't explained this well. Thanks to anyone who can solve this problem for me!