Divide Current Matrix Value by Reference Value in same Matrix (trend calculation)

Hello ab_99 

It would have been helpful to have a sample data file for reference; however, I created some sample data based on the screenshot you shared. Using that, I have developed a DAX measure that should be of assistance.

Below is the screenshot of the sample data I prepared:

Here is the simple DAX measure for the natural values with a simple summation aggregation:

Total Values = SUM(TrendCalcTable[Value])

Since you haven't specified what exactly to do with the earliest month and year value, such as whether to divide it by the reference value or keep it as is, I have created two measures to accommodate both conditions.

This is the DAX measure that does not divide the reference value by the earliest year and month value in the matrix:

TrendCalc_opt1 = 
VAR _earliestYear =
    CALCULATE ( MIN ( TrendCalcTable[Year] ), ALL ( TrendCalcTable ) )
VAR _earliestMonth =
    CALCULATE ( MIN ( TrendCalcTable[Month] ), ALL ( TrendCalcTable ) )
VAR _refValue =
    CALCULATE (
        [Total Values],
        FILTER (
            ALL ( TrendCalcTable ),
            TrendCalcTable[Year] = _earliestYear
                && TrendCalcTable[Month] = _earliestMonth
        )
    )
RETURN
    IF (
        SELECTEDVALUE ( TrendCalcTable[Year] ) = _earliestYear
            && SELECTEDVALUE ( TrendCalcTable[Month] ) = _earliestMonth,
        [Total Values],
        DIVIDE ( [Total Values], _refValue )
    )

Here is the DAX measure that divides the reference value by all the values in the matrix. We simply need to remove the IF condition from the RETURN statement.

TrendCalc_opt2 = 
VAR _earliestYear =
    CALCULATE ( MIN ( TrendCalcTable[Year] ), ALL ( TrendCalcTable ) )
VAR _earliestMonth =
    CALCULATE ( MIN ( TrendCalcTable[Month] ), ALL ( TrendCalcTable ) )
VAR _refValue =
    CALCULATE (
        [Total Values],
        FILTER (
            ALL ( TrendCalcTable ),
            TrendCalcTable[Year] = _earliestYear
                && TrendCalcTable[Month] = _earliestMonth
        )
    )
RETURN
DIVIDE ( [Total Values], _refValue )

Here is the screenshot that shows the result:

Here is the Power BI file for your reference: Power BI File (Link expires on Sep 12, 2024) 

Best Regards,
Udit

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree