Seeking Assistance with Average Employee-Citizen Ratio Calculation in Elderly Care Reporting

Hello Targit BI forum,

I need help understanding a calculation. I work with data in the elderly care sector, and there is a significant focus on obtaining visible management information about the continuity of elderly care.

I have set up a report in the form of a form where different service categories can be selected, reflecting the functional level of citizens. I would like to visualize the average number of employees per citizen for the two variables "Organization level 4" and "Organization level 5," as well as for the total.

Below is the graph: As it appears, the average does not match "Organization level 4" and the total.


How can I get the correct averages here?

Best regards, Lars Kjær Herning Kommune



  • Hi Lars

    If I understand you correctly, I think there are a few calculations to prepare the final average.

    We need to know how many citiziens are a part of each group on level5, level4 and total to be able to make the calculation.

    I came up with something like this:

    1. calculation (leaves) just sets the value 1 on each entry (not subtotal) as a beginning:
    if allcount(d1, all(c), m1) = 0 then 1 else 0

    2. calculation (members of country) sums up these leaves to get the right number of members on the lowest subtotal level:
    sum(c1, all(c), m1)

    3. calculation (members of territory) sums up on next level:
    sum(c2, all(c), m1)

    4. calculatoin (members on all level) uses this result to get to the next level:
    sum(c3, all(c), m1)

    5. calculation (members on each level) sums up all these 4 calculated columns - and ends up with the numbers of members on each level:
    sum(c1:c4, 0, m1)

    6. calculation just divides the original numbers with the last calculation column and (hopefully) get's the right result:
    sum(d1, 0, m1) / sum(c5, 0, m1)

    And then I guess you want to hide the citizens with a visibility agent like this:
    allcount(d1, all(c), m1) = 0

    Is that what you wanted, or...?



  • Hi Niels, that was exactly what I needed - thank you very much. I've been struggling with these aggregations to a higher level, but now, with your help on 2 support cases, I hope I can utilize this knowledge in the future. Best regards, Lars Kjær


Please sign in to leave a comment.

Didn't find what you were looking for?

New post