Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
Solved! Go to Solution.
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)
Proud to be a Super User!
Hi,
Please check the below picture and the attached pbix file.
It is for creating a new table.
Hi,
Please check the below picture and the attached pbix file.
It is for creating a new table.
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)
Proud to be a Super User!