Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
manojsubram
Frequent Visitor

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

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

 

TicketStrategyValues
T1ST14500
T1ST14600
T2ST25000
T2ST24500
T2ST25200
T3ST12200
T3ST12400
T4ST26000
T4ST25500
T4ST26100

 

Table 2

TicketStrategyNet ValueLogic (This reference. No need to be in the table)
T1ST1100If ST1, then largest value - smallest value
 = 4600-4500 = 100
T2ST2-1200If ST2, then 2 * smallest value - first largest - second largest
 = 2 * 4500 - 5200 - 5000
= -1200
T3ST1200If ST1, then largest value - smallest value
 = 2400 - 2200 = 200
T4ST2-1100If ST2, then 2 * smallest value - first largest - second largest
 = 2 * 5500 - 6100 - 6000
= -1200

 

Thank you!

2 ACCEPTED SOLUTIONS
ryan_mayu
Super User
Super User

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

1.PNG





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

Proud to be a Super User!




View solution in original post

Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new table.

 

Picture1.png

 

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
)
)
)

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

View solution in original post

2 REPLIES 2
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new table.

 

Picture1.png

 

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
)
)
)

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.
ryan_mayu
Super User
Super User

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

1.PNG





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

Proud to be a Super User!




Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors