Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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 , Can you share sample data and sample output in table format?
@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 |
Difference = var _1 = Minx(filter(Sheet1,[Project #] =EARLIER([Project #]) && [Bid Amount] <EARLIER([Bid Amount])),[Bid Amount])
return if(ISBLANK(_1),BLANK(),[Bid Amount]-_1)
@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 |
@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)
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |