Sum on dynamic filter selections

michael.chefer
michael.chefer
in Modeling

Looking for a way to sum off a data module based on dynamic user selections.

My dashboard has a filter(dimensioned by user). Users select any combination of these dimensions(except amount and account). Sometimes fields are blank so a simple SUM wouldn't work.

Ultimately the summation would live in a P&L statement so also need to consider time and accounts. But the main issue I'm having is getting a sum on the dimensions based on user selections.

Data module:

image.png

Dynamic filter:

image.png

Tagged:

Best Answer

  • Ashutosh
    Ashutosh
    Answer ✓

    Hi Michael,


    Assuming P&L statement is dimensioned by Time & Accounts. As you mentioned results should be dynamic selection i.e user based.
    Please follow below steps to achieve the same:

    1. Create a filter line item which will give the valid combination on the basis of dynamic selection. ( I think which u have already created by using actuals list and user list) .
      image.png

    Note: I've taken only two combination i.e entity and departments selection just for reference. You can apply all conditions.

    2. Create a another module or subsidiary view in data module but dimension should be Actuals & Users. Add below line items and update the formula as shown in below screenshot:

    image.png

    3. Create a final target module dimensioned by account, Time & User and update the formula as shown in below screenshot:

    image.png

    Let me know if you have any issues. If you are not using the user as dimension for selection then u don't need to apply user as dimension anywhere. Just follow same steps.

    Thanks

    Ashutosh

Categories