Forum Discussion
Solved
Help to Calculate target column base on Source Column and Target Column itself
Hi all, I have some problem and dont know how to write DAX to get value at Target Column (pleass see the picture) Anyone can help me to slove this? Many thanks!
Hi CongPham ,
You could use POWER() function and SUMX() function to do it.
You need two measures:
Power = VAR a = CALCULATE ( MAX ( 'Table'[No] ), ALLSELECTED ( 'Table' ) ) RETURN POWER ( 0.25, a - SELECTEDVALUE ( 'Table'[No] ) ) * SELECTEDVALUE ( 'Table'[Value] )Target = VAR a = CALCULATE ( MAX ( 'Table'[No] ) ) RETURN ROUND ( 0.75 * SUMX ( FILTER ( ALLSELECTED ( 'Table' ), 'Table'[No] <= MAX ( 'Table'[No] ) ), [Power] ) + POWER ( 0.25, a ) * 466, 0 )Here is my result.
Hi CongPham ,
The trick is to find the regulation and establish a general formula. Please refer to the following image:
Hi CongPham ,
You could use POWER() function and SUMX() function to do it.
You need two measures:
Power =
VAR a =
CALCULATE ( MAX ( 'Table'[No] ), ALLSELECTED ( 'Table' ) )
RETURN
POWER ( 0.25, a - SELECTEDVALUE ( 'Table'[No] ) )
* SELECTEDVALUE ( 'Table'[Value] )Target =
VAR a =
CALCULATE ( MAX ( 'Table'[No] ) )
RETURN
ROUND (
0.75
* SUMX (
FILTER ( ALLSELECTED ( 'Table' ), 'Table'[No] <= MAX ( 'Table'[No] ) ),
[Power]
)
+ POWER ( 0.25, a ) * 466,
0
)
Here is my result.
v-eachen-msft Many many thank
I got the true value I need, but I still don't understand your formula's meaning.
Could you give some advice?
Hi CongPham ,
The trick is to find the regulation and establish a general formula. Please refer to the following image: