2 Replies Latest reply on Apr 13, 2018 9:57 AM by Joel Dodd

    Incorporating monthly parameters in aggregate calculations

    Joel Dodd

      I have a series of income and expenses that are calculated at a row level and the end user also wants to enter a monthly 'additional' income and monthly 'additional' expense amounts via a parameter.  These also vary by fund type and they would like to filter based on the fund type and other dimensions.  I need to use a combination of these amounts to come up with a net profit.   I started looking at level of detail calculations but couldn't seem to get this to work with the combination of requirements for the scenario.  Either the filtering would not work or the level of detail calculations showed a higher amount for the monthly amounts.  The attached workbook contains a simplified version of what I am trying to accomplish but the only way I could figure out to do this is to ask the end user to enter the number of records per fund type to calculate the per record amount of the monthly income and expenses.  This is drawing scrutiny since I have to provide them the number of records per fund type just to have them enter the displayed value into a parameter in order to get the calculation to work at a row level. 

       

      I would either like to automatically set the parameter value based on the count by fund types or get an aggregate level calculation result (i.e. monthly expense / number of records per fund type) persisted at the row level.  Neither of which seems possible based on what I have been researching.  Any ideas on this one?