Table Calculations

[RAHULBANDI]

Hi Team,

Name	Amount
Raj	100
Raj	100
Sri	200
Sri	200
Anu	300

Expected Output:

Name	Amount	Name1	Amount1	Name2	Amount2
Raj	100	Raj	100	Blank	Blank
Raj	100	Raj	100	Blank	Blank
Sri	200	Blank	Blank	Sri	200
Sri	200	Blank	Blank	Sri	200
Anu	300	Blank	Blank	Blank	Blank

I'm looking for following Output with help of DAX

[v-binbinyu-msft · Accepted solution]

Hi @RAHULBANDI ,

Please try below steps:

1. add a index column with power query pane, because

2. add measure with below dax formula

Name1 =
VAR cur_id =
    SELECTEDVALUE ( 'Table'[Index] )
VAR cur_name =
    SELECTEDVALUE ( 'Table'[Name] )
VAR cur_at =
    CALCULATE ( MAX ( 'Table'[Amount] ), 'Table'[Index] = cur_id )
RETURN
    SWITCH ( TRUE (), cur_id = 1, cur_name, cur_id = 2, cur_name, "Blank" )

Amount1 =
VAR cur_id =
    SELECTEDVALUE ( 'Table'[Index] )
VAR cur_name =
    SELECTEDVALUE ( 'Table'[Name] )
VAR cur_at =
    CALCULATE ( MAX ( 'Table'[Amount] ), 'Table'[Index] = cur_id )
RETURN
    SWITCH ( TRUE (), cur_id = 1, cur_at, cur_id = 2, cur_at, "Blank" )

Name2 =
VAR cur_id =
    SELECTEDVALUE ( 'Table'[Index] )
VAR cur_name =
    SELECTEDVALUE ( 'Table'[Name] )
VAR cur_at =
    CALCULATE ( MAX ( 'Table'[Amount] ), 'Table'[Index] = cur_id )
RETURN
    SWITCH ( TRUE (), cur_id = 3, cur_name, cur_id = 4, cur_name, "Blank" )

Amount2 =
VAR cur_id =
    SELECTEDVALUE ( 'Table'[Index] )
VAR cur_name =
    SELECTEDVALUE ( 'Table'[Name] )
VAR cur_at =
    CALCULATE ( MAX ( 'Table'[Amount] ), 'Table'[Index] = cur_id )
RETURN
    SWITCH ( TRUE (), cur_id = 3, cur_at, cur_id = 4, cur_at, "Blank" )

3. add a table visual with table fields and measure

Please refer the attached .pbix file.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[daXtreme]

Mate, maybe you'd like to say anything about how you arrive at the output? For someone to be able to help you, this someone must have a clear understanding of the rules that lead to the end result. From your post it's about as clear as mud how to arrive at the table. Thanks.

Please read this first: How to Get Your Question Answered Quickly - Microsoft Power BI Community