Need help to split multiple values in one column for the duplicated rows.

[slhyrkl]

As shown in the picture I attached, There is one part (part no 127316) has multiple values in the next column. I want to split those values into different columns based on the same part no. 78.96% is the current month's value and 53,51% is the last month's value and I want to create two different columns as "Current month", "Last month" and split these values into these columns. I have multiple parts that have multiple percentages but some of the parts only have one percentage. The upper value (78.96% ) is the current month and the below value (53,51%) is the last months value. This is the case for the other part numbers too. My main goal is to show the data like this and calculate the difference between months if there are values for two months for a part. How can I transform the data to achieve this?

[ryan_mayu · Accepted solution]

@slhyrkl 

actually i don't suggest you to pivot table. 

you can create a column

Column =

VAR a=minx(FILTER('Table','Table'[c]=EARLIER('Table'[c])&&'Table'[d]<EARLIER('Table'[d])),'Table'[d])

return if(CALCULATE(count('Table'[d]),ALLEXCEPT('Table','Table'[c]))=1,"Current",if(ISBLANK(a),"last","Current"))

then you can create a measure to calcualte difference

Measure = CALCULATE(sum('Table'[d]),'Table'[Column]="Current") - CALCULATE(sum('Table'[d]),'Table'[Column]="last")

pls see the attachment below