I am trying to do a report on contracted revenue per customer per month, but the structure of my data is making life very difficult.
I have a Contract Header that has the number of People. This has a 1:N relationship with Contract Lines that each have a Product. This has a 1:N relationship with Contract Line items have the Amount and the Month.
So I have Month as the column header, and as a measure I need to work out Amount per person which I can't seem to do. I can get the correct Amount and People as measures, but if I try dividing one by the other the lines are just blank. I assume that the problem is with setting the aggregate level or the summary calculation but I'm not sure what they should be.
I just need total Amount / total People, whichever way is easiest!
Thanks in advance,