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
    4 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

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

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