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

[ab_99]

Hi,

I have a matrix, with years as rows and months as columns. 

I want to perform a trend calculation. Herefore, I need a reference value - in this case it is the first number of the earliest month+year, 1.834,00.

Now I want to divide the upcoming months by this reference value, e. g.:

• for 2023-02: 8.086,50/1.834,00
• for 2024-01: 1.376,00/1.834,00

But again, the reference value must be for all other upcoming years the first value of the earliest date period.

Can someone help me with that? 

Thank you very much in advance!

[quantumudit · Accepted solution]

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