Forum Discussion

hwoehler's avatar
hwoehler
Helper I
6 years ago
Solved

Create Lower Triangular Matrix

Hello everybody,

I would like to implement the following:

I have a DateTable in Power BI, a table "FSE" with data, and then a filtered Table table. This contains the dates of the last date of the year (relative to "FSE") and the corresponding value. I would like to visualize a lower triangular matrix that looks like this: (The color formatting is unimportant. The calculations/values ​​are important)

desired result

In Excel I did this manually, the files are attached (pbix and excel file): https://we.tl/t-x5Zi1P1Zw1
For Power BI I have not been successful so far. Can someone help me?

 

Best regards, hwoehler

 

 

Information about calculations:

The calculation works according to the formula: new value = ((final value / initial value) ^(1 / n)) - 1

The divisor (initial value) is fixed per column (the year on the x-axis). It is a constant (per column!). The dividend (= the final value) increases by 1 until it reaches the year 2018 (maximum of year). The final value is therefore flexible. The exponent also changes the value n (= the duration in years). This increases logically over the years. Columnwise, one line is always started later, as before.

 

How the calculations work, you can see on the pictures here: https://we.tl/t-hEQNrFJ9FS

Need Help
  • parry2k's avatar
    parry2k
    6 years ago

    hwoehler solution is attached, the core of it is following DAX measure

     

    Value = 
VAR __yearonColumn = CALCULATE( MAX ( FSE[Year Column] ), ALLEXCEPT( FSE, FSE[Year] ) )
VAR __cYear =MAX ( DateTable[Year] )
VAR __pYear = __cYear - 1
VAR __prevYear = 
 CALCULATE( 
    [Parv Last Value of Year],
    ALL ( DateTable[Year]),  FSE[Year] = __pYear )
VAR __currYear =
 CALCULATE( 
    [Parv Last Value of Year],
    ALLEXCEPT( FSE, FSE[Year] )
)
VAR __identity =  __yearonColumn - __pYear 
RETURN
IF ( __identity > 0 && __prevYear <> BLANK(),
    (
        DIVIDE( __currYear, __prevYear ) ^ 
        DIVIDE( 1, __identity )
    ) - 1
)

5 Replies