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