1 Reply Latest reply on Dec 11, 2018 4:20 PM by Patrick Van Der Hyde

# Formula based on calculated field in a hierarchy

Hi everyone,

I'm having trouble figuring out how to, and if it's even possible, to get my formula to work correctly  based on a calculated field that is in a hierarchy.

I have a hierarchy that has 3 levels:

-region

-country

-budgeted

Each level of the hierarchy has a separate fundraising budget, but everything is rolled-up from the lowest level. For example, in our Southeast asia region, we have offices in:

-Thailand

-Philippines

-Cambodia

Cambodia only has one office, so the budgeted level and country level are on in the same. For Philippines, we have offices and separate budgets in Cebu and Manila. There is also a national budget that is separate from the offices and a separate part of the country overall budget. So for the Philippines the budget hierarchy is:

Manila+Cebu+National=Philippines

So far so good? Here's the problem: the national budget can be used to cover the office budgets if they are underfundraised but not vice versa. So if Manila and Cebu are over funded by a total of \$1m and the national budget is under by \$1m, then the Philippines as a whole is considered underfunded by \$1m. However, if the national budget is over funded by \$1m and Cebu and Manila are underfunded by \$1m total, then the Philippines as a whole would be considered fully funded.

To the regional level, countries with under-funded totals (that's offices+national budgets as described above) add to the regional fundraising deficit. As with offices, over funded countries cannot have their overages put towards other countries within the region.

So that's all to get to my problem, I have the individual office/national budgets set and the country budgets set as well. However, since the 3 hierarchy levels are kinda overlapping due to country budgets being part of two levels, I have 2 different formulas for the two lowest levels of the hierarchy. For the regional level, it needs to be calculated based on the result at the country level, which is in turn calculated as a result of the budgeted level. I can't just sum them because that would be an aggregation of an aggregation and the value of the country-level total changes dynamically depending on which levels of the hierarchy are displayed.

This is a very long winded explanation of my situation so I hope it's clear. I know everyone likes seeing packaged workbooks but my datasource is SalesForce and my extracts are too large to upload to the forums (this one is 130mb), google drive link in case you are willing to take a look Restricted Giving Report v5.twbx - Google Drive

For this regional view, Southeast Asia should be calculated from the country level as \$701,397 - \$106,630=\$594,767 (Null at the country level is money given directly to the region and can be used to cover any underfundraised amounts)

and Africa region should be \$2,166,693

It may be that without being able to see the workbook it's not possible to help with a definitive answer but any general guidance on calculating values within hierarchies would be appreciated!!!

In case someone is willing to download a big file: Restricted Giving Report v5.twbx - Google Drive

Here are my relevant formulas:

Budget remaining for the year:

{ FIXED [GAU]: sum([FY Budget])}- { FIXED [GAU]:max([Weighted Allocations+Grants+Rollover])}

Fundraising remaining for the year at the lowest level:

IF [Budget Remaining]>0 Then [Budget Remaining]

Else 0

END

Fundraising remaining for the year at the country level:

IF  sum({ FIXED [GAU]:max(IF [Fundraising Remaining GAU Level]>0

THEN [Fundraising Remaining GAU Level]

ELSEIF [GAU]='Philippines' OR

[GAU]='Thailand' OR

[GAU]='South Asia' OR

[GAU]='Uganda' Then

[Budget Remaining]

ELSE 0

END)})>0

THEN

sum({ FIXED [GAU]:max(IF [Fundraising Remaining GAU Level]>0

THEN [Fundraising Remaining GAU Level]

ELSEIF [GAU]='Philippines' OR

[GAU]='Thailand' OR

[GAU]='South Asia' OR

[GAU]='Uganda' Then

[Budget Remaining]

ELSE 0

END)})

ELSE 0

END

• ###### 1. Re: Formula based on calculated field in a hierarchy

Are you still working on this.  I downloaded your workbook (by the way everyone it is version 10.5) and looked.  Thank you for supplying a workbook as it helps a lot to follow along.

I do not see this condition:

"However, if the national budget is over funded by \$1m and Cebu and Manila are underfunded by \$1m total, then the Philippines as a whole would be considered fully funded."  In the data as all of the countries I see on the GAU level have negative balances or if they have a positive balance then they have no other offices.

so is the primary issue that you need those values associated with Null for the GAU to be incorporated into the totals on the Regional level?

Let us know.

Patrick