Forum Discussion
Divide one value by reference value in a matrix (trend calculation)
Hi dear Power BI Community,
I have this following issue:
I have a matrix with years in rows and months in columns.
Now I want to perform a trend calculation as follows:
Value from 2023-1 (1.834) is for all following periods the reference value (the value from the earliest month and year basically). I need to devide each value from the other months by the reference value.
E.g.:
2024-02: 2.618/1.834
2023-04: 12.498/1.834
and so on.
How can I achieve this with a measure?
Thanks in advance!
would you try this solution:
var min_date= calculate(min(date), allselected(table))
var ref = CALCULATE(max(X), filter(ALLSELECTED(table), [date] = min_date)
return
divide ( selectedvalue( X) , ref)
** X is the column that contain tehse values **
if still not working, please provide some details about your tables, model and relationships.
6 Replies
- Selva-Salimi
Solution Sage
Hi ab_99
Is it a fixed value for all month and all years?! if so, then you can write a measure as follows:
measure rate :=
var ref = calculate (max(X) , filter (all(table) , year(date)= 2023 && month (date) =1))
return
divide ( selectedvalue( X) , ref)
** X is the column that contain tehse values **
If this post helps, then I would appreciate a thumbs up 👍 and mark it as the solution to help the other members find it more quickly.
Hi Selva-Salimi
thank you for your quick reply.
The month and year isn't fixed. I have a between-slicer where I select the year-frame I'd like to see.
The value has to be variable and must come from the first month of the earliest year selected.- Selva-Salimi
ok, then you can rewrite as follows:
var ref = CALCULATE(max(X), filter(ALLSELECTED(table), [date] = min (date))
return
divide ( selectedvalue( X) , ref)
** X is the column that contain tehse values **
If this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.
