Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Multivariable Matrix with User Input

See pictures below to help with explanation. 
Summary: I am receiveing a string of data with missing data points. I need to calculate the missing data points as well as react to user input. The user input could affect the calculation and/or the data shown. I've hit a dead end with both calculated columns (how can I capture user activity and use it in the calculated column formula?) as well as mutiple measure to represent the months (how do I get the beginning balance from the prior month/column?). 

 

Below is an example of the data I receive as well as a few examples showing ways the user wants the result displayed. Any ideas?

 

 

Need Help

6 Replies

  • sturlaws's avatar
    sturlaws
    Resident Rockstar
    7 years ago

    Hi, Anonymous 

    you could try to use a measure and a parameter for user input:

    AmountMeasure =
VAR beginingbalance =
    CALCULATE (
        SUM ( accounts[amount] );
        FILTER (
            ALLEXCEPT ( accounts; accounts[account] );
            accounts[month]
                <= MIN ( [month] ) - 1
                && accounts[item] <> "income 1"
        )
    )
        + CALCULATE (
            SUM ( accounts[amount] );
            FILTER (
                ALLEXCEPT ( accounts; accounts[account] );
                accounts[month]
                    <= MIN ( [month] ) - 1
                    && accounts[item] = "income 1"
            )
        ) * [User input Value income 1]
VAR endingbalance =
    CALCULATE (
        SUM ( accounts[amount] );
        FILTER (
            ALL ( accounts );
            accounts[account] IN VALUES ( accounts[account] )
                && accounts[month] <= MIN ( [month] )
                && accounts[item] <> "income 1"
        )
    )
        + + CALCULATE (
            SUM ( accounts[amount] );
            FILTER (
                ALLEXCEPT ( accounts; accounts[account] );
                accounts[month] <= MIN ( [month] )
                    && accounts[item] = "income 1"
            )
        ) * [User input Value income 1]
RETURN
    SWITCH (
        TRUE ();
        MAX ( accounts[item] ) = "ending balance"; endingbalance;
        MAX ( accounts[item] ) = "begining balance"; beginingbalance;
        MAX ( accounts[item] ) = "income 1"; CALCULATE (
            SUM ( accounts[amount] );
            FILTER (
                ALLEXCEPT ( accounts; accounts[account]; accounts[month] );
                accounts[item] = "income 1"
            )
        ) * [User input Value income 1];
        SUM ( accounts[amount] )
    )

    The measure in this code called [User input Value income 1] is from the parameter

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      This is great. I appreciate you working on this. 

      I am using the measure now and it is acting unexpectedly. I am seeing amounts for Income 1 for every month, and not just the months where there is income. Also, the beginning balance looks like it is aggregating prior month ending balances for all accounts. 

      • sturlaws's avatar
        sturlaws
        Resident Rockstar
        7 years ago

        I have tested it for the sample data you provided in the first post, perhaps you could provide a larger sample set? And preferably not provide data as screenshots, as I then have to manually input the values