Forum Discussion

mrothschild's avatar
mrothschild
Continued Contributor
5 years ago
Solved

Calculating % change from a matrix table

Link to PBIX file is: https://drive.google.com/file/d/1j_4CrUj0fPh0xK6hnO7OZ-uIWkoP5Kdg/view?usp=sharing

 

I'm trying to take the following Matrix output and calculate the natural log, or LN([value (t)]/[value (t-1)] ^ (1 / ([year (t) - [year (t-1)] for each given observation except for the first chronologically in a given row (because there is no (t-1) value).  You'll also note from the table that (t-1) isn't always available, in which case, I'd want (t-[most recent data point prior]) and the associated year. To complicate issues further, what's shown below is for a single model type.  But when the data is less sliced, the Values in the Matrix are unit-weighted (via a Measure), and so I'd like to keep that unit-weighting as part of the calculation base, rather than just using Values.

 

From that calculation, I want to calculate the average and standard deviation on a row-by-row basis of the output.  A screenshot of the desired output from Excel is shown below the Power BI Matrix screen shot.

 

 

 

 

 

1 Reply