How to filter values based on non-source model dimensions

Options
dspar001
dspar001
in Modeling

I've got models A, B and C. A is our forecasted IS. B is an IS of confidential acquisitions. B has one additional dimension that A doesn't have - the list of confidential aquisitions. Connected to B is a SYS module that allows our Treasurer to check a Boolean that controls whether a confidential project flows through to the final module, C. Module C = the sum of A and B and is dimensioned by a subset of the confidential projects list, which includes nothing but the top-level item. Is there a way to filter what flows through from B by the SYS module, without creating a module in between B and C or changing the subset of C? The reason for the ask is due to size.

I've tried various formulas, but because module C is dimensioned by the top-level of confidential acquisitions list and B and the SYS module are dimensioned by the entire list, it doesn't seem to want to work. I was thinking this is something like a SUM formula, but more of a: "SUM values of "Confidential Project 1" if "Confidential Project 1" is marked TRUE in the SYS module". Is there a way to write that? + 10 points if it's a highly sustainable formula as well - ie. if we don't have to update and map a SYS module and the formula everytime our Treasurer adds a new list item

Answers

  • JaredDolich
    JaredDolich
    Options

    @dspar001

    Can you create another line item in "B" that only shows the forecast if the Treasurer has clicked the Boolean to include? So, if Treasure checks TRUE then forecast else zero. Then sum the new line item into "C".

Categories