cancel
Showing results for
Did you mean:  Helper I

## 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! 2 ACCEPTED SOLUTIONS  Community Support

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. If this post helps, then please consider Accept it as the solution to help the other members find it.  Community Support

Hi @CongPham ,

The trick is to find the regulation and establish a general formula. Please refer to the following image: If this post helps, then please consider Accept it as the solution to help the other members find it.
6 REPLIES 6  Community Support

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. If this post helps, then please consider Accept it as the solution to help the other members find it.  Helper I

@v-eachen-msft  Many many thank
I got the true value I need, but I still don't understand your formula's meaning.  Community Support

Hi @CongPham ,

The trick is to find the regulation and establish a general formula. Please refer to the following image: If this post helps, then please consider Accept it as the solution to help the other members find it.  Super User IV

@CongPham

872 target should be for line no 3, please check

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

Proud to be a Super User!  Helper I

Ahh, Sorry, I'm capturing it with excel. Please see the full picture:   Super User IV

@CongPham

I am getting this: Proud to be a Super User! Announcements #### Welcome to the User Group Public Preview  