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

[T1n4]

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 development in time.

We are trying to calculate predictions of their development as follows:

For this purpose, we prepared a new measure that calculates Development factors:

Is there some way to use more values from one measure/column and multiply by it the other values as displayed in the second picture? Using Calculate, we were trying to calculate "rows" parallel to the diagonal - each separately. However, we are not able to figure out how to multiply it with these factors. Is there another way to solve this problem (with measures or calculated columns...)? Thank you all for any ideas. ❤️

[T1n4]

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 · Accepted solution]

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]

Thank you very much for your help 🙂 !

[v-cazheng-msft]

Hi @T1n4,

You may try this solution.

Here are the sample data.

Sample Table:

Development factors Table:

1 Build one-many relation between these two tables

2 Create a Calculated column in Development factor Table to multiply the FactorVal contained in this table

AccumulatedFactor =
CALCULATE (
    PRODUCT ( 'Development factors'[FactorVal] ),
    FILTER (
        'Development factors',
        'Development factors'[Prediction_period]
            <= EARLIER ( 'Development factors'[Prediction_period] )
    )
)

3 Create a measure to help you calculate the predication values

Values With Prediction =
VAR total =
    SUM ( 'Sample Table'[Amt] )
VAR prevVal =
    CALCULATE (
        MAX ( 'Sample Table'[Amt] ),
        FILTER (
            ALL ( 'Development factors' ),
            'Development factors'[Prediction_period]
                < MAX ( 'Development factors'[Prediction_period] )
        )
    )
RETURN
    IF (
        ISBLANK ( total ),
        prevVal * MAX ( 'Development factors'[AccumulatedFactor] ),
        total
    )

Then, the visual looks like this.

Also, attached the pbix file. To get your expected result, you need extra steps to filter the data you want by using functions like IF.

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