Forum Discussion

T1n4's avatar
T1n4
Frequent Visitor
4 years ago
Solved

Multiplying values in visualisation by more than one value calculated with a measure

Hello, We have an issue with computing predictions. Using DAX, we created a matrix with total amounts – the first column is for new amounts that appeared in the quarter, and on the rows is their dev...
  • v-cazheng-msft's avatar
    v-cazheng-msft
    3 years ago

    Hi T1n4,

     

    Please try this solution. Firstly, you need add a row in your Development factors which is called Cumulative factors in my sample file table for your expected result.

     

    Build relationship between these two tables.

     

    Create a Measure to do the calculation.

    Values With Prediction =
VAR Amt =
    SUM ( 'Sample Table'[Amt] )
VAR MaxPeriod =
    CALCULATE (
        MAX ( 'Sample Table'[Period] ),
        ALLEXCEPT ( 'Sample Table', 'Sample Table'[YM] )
    ) + 1
VAR FactVal =
    CALCULATE (
        MAX ( 'Sample Table'[Amt] ),
        FILTER (
            ALL ( 'Cumulative factors' ),
            'Cumulative factors'[Prediction_period]
                < MAX ( 'Cumulative factors'[Prediction_period] )
        )
    )
VAR factors =
    MAX ( 'Cumulative factors'[FactorVal] )
RETURN
    IF (
        ISBLANK ( Amt ),
        PRODUCTX (
            FILTER (
                ALL ( 'Cumulative factors' ),
                'Cumulative factors'[Prediction_period] >= MaxPeriod
                    && 'Cumulative factors'[Prediction_period]
                        <= MAX ( 'Cumulative factors'[Prediction_period] )
            ),
            'Cumulative factors'[FactorVal]
        ) * FactVal,
        Amt
    )

     

    Then the result will look like this.

     

    For more details, please refer to the attached sample file.

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please let me know. Thanks a lot!

     

    Best Regards,    

    Community Support Team _ Caiyun

T1n4's avatar
T1n4
Frequent Visitor
4 years ago

Hello, v-cazheng-msft, 

Thank you for your response. However, your solution didn't solve my problems. The table should look like this:

 

Factor one should multiply value only in the last row; it should not be used in any other multiplications. Factor 2 should be used only in the last two rows… and so on.

Please, let me know if you have any further ideas.

Thank you!

  • v-cazheng-msft's avatar
    v-cazheng-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi T1n4,

     

    Please try this solution. Firstly, you need add a row in your Development factors which is called Cumulative factors in my sample file table for your expected result.

     

    Build relationship between these two tables.

     

    Create a Measure to do the calculation.

    Values With Prediction =
VAR Amt =
    SUM ( 'Sample Table'[Amt] )
VAR MaxPeriod =
    CALCULATE (
        MAX ( 'Sample Table'[Period] ),
        ALLEXCEPT ( 'Sample Table', 'Sample Table'[YM] )
    ) + 1
VAR FactVal =
    CALCULATE (
        MAX ( 'Sample Table'[Amt] ),
        FILTER (
            ALL ( 'Cumulative factors' ),
            'Cumulative factors'[Prediction_period]
                < MAX ( 'Cumulative factors'[Prediction_period] )
        )
    )
VAR factors =
    MAX ( 'Cumulative factors'[FactorVal] )
RETURN
    IF (
        ISBLANK ( Amt ),
        PRODUCTX (
            FILTER (
                ALL ( 'Cumulative factors' ),
                'Cumulative factors'[Prediction_period] >= MaxPeriod
                    && 'Cumulative factors'[Prediction_period]
                        <= MAX ( 'Cumulative factors'[Prediction_period] )
            ),
            'Cumulative factors'[FactorVal]
        ) * FactVal,
        Amt
    )

     

    Then the result will look like this.

     

    For more details, please refer to the attached sample file.

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please let me know. Thanks a lot!

     

    Best Regards,    

    Community Support Team _ Caiyun

    • T1n4's avatar
      T1n4
      Frequent Visitor
      3 years ago

      Thank you very much for your help 🙂 !