dynamically divide a set value by number of rows displayed by an expanded dimension

[#1]

Hello,

I am trying to figure out how to write a calculation to evenly estimate a value for a territory budget based off of a total shared district budget. See attached example.

For example:

I have 3 districts, each with a set $500 budget. Hypothetically, I want to split the total budget up evenly amongst the available number of territories but that will fluctuate in number with time. Therefore, for District A, only 2 territories have a purchase so the total budget of $500 needs to be divided by 2 to display $250 each, whereas District B has 3 ($166.67 each) and District C has 4 ($125 each). When the territory dimension is collapsed, the district total for all should add up to $500.

How would this be done??

Thanks

[lironbaram]

hei

attach is an example of what you asked for

[#3]

Thank you Liron. I have tried this expression and it only returns a total for some fields and not others. Would you please help me to understand the components that make up the following expression so that I may make amendments to suit my needs?

sum(aggr(BUDGET/ Count(DISTINCT Total <DISTRICT> TERRITORY),TERRITORY,DISTRICT))

[#4]

Hello QV gurus,

I am still troubled by this issue and would like to see if anyone can help explain how I may arrive at a successful solution to my problem.

I am returning the correct calculated value for some but not all rows. I do not understand how this calculation is functioning. Can anyone help to clarify??

sum(aggr(BUDGET/ Count(DISTINCT Total <DISTRICT> TERRITORY),TERRITORY,DISTRICT))

Thanks!