cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CongPham
Helper I
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!

formula2.jpg

 

2 ACCEPTED SOLUTIONS
v-eachen-msft
Community Support
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.

1-1.PNG

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

Hi @CongPham ,

 

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

1-2.PNG

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

6 REPLIES 6
v-eachen-msft
Community Support
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.

1-1.PNG

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

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

1-2.PNG

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

Fowmy
Super User IV
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 🙂

YouTube, LinkedIn

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn

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

formula2.jpg

 

@CongPham 

I am getting this:

Fowmy_0-1596009748979.png

 

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors