Calculate between rows based on another column

[summit20]

I have a bid sheet with raw bid data as shown in the table below. Every project I enter my bid and am always the top bidder in the row (CCI). I am trying to create another column that shows the difference between my bid and every other bid. I plan to filter this data by the project number so the filtered data will only show one row that says CCI as the bidder. The unfiltered version shows multiple rows that have CCI because it shows every project. How can I show the difference between the bids for each project?

 

[amitchandak]

@summit20 , Can you share sample data and sample output in table format?

[summit20]

@amitchandak Here is what the raw data would look like. Notice the difference column would be some sort of calculated column.

Project #	Bidder	Bid Amount
1	CCI	10
1	ABC	11
1	DEF	12
2	CCI	50
2	ABC	55
2	DEF	60
2	GHI	65

Here is what I would hope the output would look like.

Project #	Bidder	Bid Amount	Difference
1	CCI	10	-
1	ABC	11	1
1	DEF	12	2
2	CCI	50	-
2	ABC	55	5
2	DEF	60	10
2	GHI	65	15

[amitchandak]

@summit20 ,

Difference = var _1 = Minx(filter(Sheet1,[Project #] =EARLIER([Project #]) && [Bid Amount] <EARLIER([Bid Amount])),[Bid Amount])
return if(ISBLANK(_1),BLANK(),[Bid Amount]-_1)

[summit20]

@amitchandak . We are so close! That works as long as CCI is the lowest bidder. I changed the Bid in Project 2 to 105 and it based the calculation off the lowest bidder. Is there a way to make all of the formulas based off of CCI's bid instead of the lowest bidder?

Project #	Bidder	Bid	Difference
1	CCI	10
1	ABC	11	1
1	DEF	12	2
2	CCI	105	50
2	ABC	55
2	DEF	60	5
2	GHI	65	10

[amitchandak · Accepted solution]

@summit20 , try like

Difference = var _1 = Minx(filter(Sheet1,[Project #] =EARLIER([Project #]) && Sheet1[Bidder]="CCI"),[Bid Amount])
return if(ISBLANK(_1),BLANK(),[Bid Amount]-_1)

[summit20]

That worked!!! Thank you so much!!! Any good resource to help understand how you built this and the logic for this formula to help me better learn PowerBI?