Help to Calculate target column base on Source Column and Target Column itself

[CongPham]

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!

[v-eachen-msft · Accepted solution]

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 · Accepted solution]

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.

[CongPham]

@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?

[v-eachen-msft · Accepted solution]

Hi @CongPham ,

The trick is to find the regulation and establish a general formula. Please refer to the following image:

[Fowmy]

@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 🙂

YouTube, LinkedIn

[CongPham]

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

[Fowmy]

@CongPham 

I am getting this: