PowerBI/Dax-Need to create a data table to calulate the net value between two or more rows

[manojsubram]

Hello All,

My current data table is coming in the way shown Table 1 and would like to convert this into table 2(as shown below). I'm open to have the table2 created by using the new data table model or Power BI visual table using DAX measures.

Net value on table 2 is calulated using logic provided in the table and it's mainly based on the strategy column. 

Note: Logic column in Table 2  just added here as an reference, no need to have the logic column to be part of the new table

I really appreciate any suggestion on meeting this requirement. 

Table 1

Ticket	Strategy	Values
T1	ST1	4500
T1	ST1	4600
T2	ST2	5000
T2	ST2	4500
T2	ST2	5200
T3	ST1	2200
T3	ST1	2400
T4	ST2	6000
T4	ST2	5500
T4	ST2	6100

Table 2

Ticket	Strategy	Net Value	Logic (This reference. No need to be in the table)
T1	ST1	100	If ST1, then largest value - smallest value  = 4600-4500 = 100
T2	ST2	-1200	If ST2, then 2 * smallest value - first largest - second largest  = 2 * 4500 - 5200 - 5000 = -1200
T3	ST1	200	If ST1, then largest value - smallest value  = 2400 - 2200 = 200
T4	ST2	-1100	If ST2, then 2 * smallest value - first largest - second largest  = 2 * 5500 - 6100 - 6000 = -1200

Thank you!

[ryan_mayu · Accepted solution]

@manojsubram 

you can try this

Measure = 
VAR _count=COUNTROWS('Table')
VAR _sum=sumx(FILTER('Table','Table'[Values]<>min('Table'[Values])),'Table'[Values])
return if(_count=2,max('Table'[Values])-min('Table'[Values]),min('Table'[Values])*2-_sum)

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new table.

New Table =

ADDCOLUMNS (

SUMMARIZE ( Data, Data[Ticket], Data[Strategy] ),

"@Result_Column",

CALCULATE (

VAR currentticket =

MAX ( Data[ticket] )

VAR newtable =

FILTER ( Data, Data[Ticket] = currentticket )

VAR _maxvalue =

MAXX ( newtable, Data[Values] )

VAR _minvalue =

MINX ( newtable, Data[Values] )

VAR _middlevalue =

MAXX (

FILTER ( newtable, NOT ( Data[Values] IN { _maxvalue, _minvalue } ) ),

Data[Values]

)

RETURN

IF (

VALUES ( Data[Strategy] ) = "ST1",

_maxvalue - _minvalue,

_minvalue * 2 - _maxvalue - _middlevalue

)

)

)

[ryan_mayu · Accepted solution]

@manojsubram 

you can try this

Measure = 
VAR _count=COUNTROWS('Table')
VAR _sum=sumx(FILTER('Table','Table'[Values]<>min('Table'[Values])),'Table'[Values])
return if(_count=2,max('Table'[Values])-min('Table'[Values]),min('Table'[Values])*2-_sum)