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.
Hi there,
I've found a few posts asking similar questions but haven't quite found one that answers my question...
I have a large data set in a similar format to the table below. Essentially, for each order, several bidders submit their price and one is picked as the winner. Within each order, there may be several sub accounts that make up the full order, but the bidders will be the same for each sub account within the order.
I'm looking to create a 'Measure' within Power BI that sums the 'Volume by Order', but only for each unique order number. The goal is to then display data based on the winners, i.e. how much volume each winning bidder has won per unique order number. I've provided a table of the ouput I'm looking for below the data table.
Order Number | Account | Product | Volume by Account | Volume by Order | Winner | Bidders | Bid |
1 | A | 12 | 500 | 500 | NAT | WBM | 225 |
1 | A | 12 | 500 | 500 | NAT | ANT | 225 |
1 | A | 12 | 500 | 500 | NAT | TOD | 225 |
1 | A | 12 | 500 | 500 | NAT | NAT | 223 |
2 | Z | 65 | 1,600 | 1,600 | JAM | YBA | 250 |
2 | Z | 65 | 1,600 | 1,600 | JAM | JAM | 247 |
3 | Z | 83 | 700 | 700 | UBT | UBT | 71 |
3 | Z | 83 | 700 | 700 | UBT | YBA | 73 |
4 | L | 12 | 8,000 | 106,000 | WBM | JAM | 225 |
4 | L | 12 | 8,000 | 106,000 | WBM | WBM | 210 |
4 | A | 12 | 5,000 | 106,000 | WBM | JAM | 225 |
4 | A | 12 | 5,000 | 106,000 | WBM | WBM | 210 |
4 | T | 12 | 28,000 | 106,000 | WBM | JAM | 225 |
4 | T | 12 | 28,000 | 106,000 | WBM | WBM | 210 |
4 | B | 12 | 5,000 | 106,000 | WBM | JAM | 225 |
4 | B | 12 | 5,000 | 106,000 | WBM | WBM | 210 |
4 | G | 12 | 50,000 | 106,000 | WBM | JAM | 225 |
4 | G | 12 | 50,000 | 106,000 | WBM | WBM | 210 |
4 | I | 12 | 10,000 | 106,000 | WBM | JAM | 225 |
4 | I | 12 | 10,000 | 106,000 | WBM | WBM | 210 |
5 | B | 58 | 800 | 800 | TOD | ANT | 87 |
5 | B | 58 | 800 | 800 | TOD | TOD | 86 |
6 | I | 79 | 800 | 800 | WBM | WBM | 398 |
6 | I | 79 | 800 | 800 | WBM | YBA | 405 |
7 | G | 38 | 2,250 | 8,500 | JAM | JAM | 200 |
7 | A | 38 | 1,750 | 8,500 | JAM | JAM | 200 |
7 | B | 38 | 4,500 | 8,500 | JAM | JAM | 200 |
Winner | Volume |
JAM | 10,100 |
NAT | 500 |
TOD | 800 |
UBT | 700 |
WBM | 106,800 |
Thanks
Solved! Go to Solution.
Hi,
Try this calculated field formula
=SUMX(SUMMARIZE(VALUES(Data[Order Number]),[Order Number],"ABCD",MIN(Data[Volume by Order])),[ABCD])
Hope this helps.
Hi,
Try this calculated field formula
=SUMX(SUMMARIZE(VALUES(Data[Order Number]),[Order Number],"ABCD",MIN(Data[Volume by Order])),[ABCD])
Hope this helps.
This doesn't allow me to look at the volume for each Winner...
Hi @Jordan1,
I am not sure of whom you are replying to but my formula seems to work fine
I created a New Measure with your code:
calc = SUMX(SUMMARIZE(VALUES(Sheet1[Order Number]),Sheet1[Order Number],"ABCD",MIN(Sheet1[Volume by Order])),[ABCD])
And then I try to present this in a "Matrix" visual and I get the below. (Apologies for my last screenshot - I think I was using 'calc' from one query, but 'Winner' from a different query)
Hi,
Are you using the same data as you have pasted above? Share the link from where i can download your PBI file.
My apologies, I'd created 'calc' as a Column. Once I created this as a Measure instead, it worked. Just applying to my full dataset now.
Many thanks
Sure. Let me know how it works.
That looks like it's working across my whole dataset as well. Thank you very much.
Are you able to explain what the formula is doing, please? I'm new to Power BI / DAX and trying to get a better feel for how to use functions.
From my interpretation:
I guess I struggle with the table / database logic in Power BI rather than the cell based focus in Excel.
Many thanks
You are welcome.
If I need to adjust the formula to count the number of times each bidder has been asked to provide a bid for an order, can I adjust the provided function?
Would something like the below work?
=COUNTX(SUMMARIZE(VALUES(Data[Order Number]),[Order Number],"ABCD",MIN(Data[Bidders])),[ABCD])
I'm just not sure what to change Min to, as this column is text data rather than numerical data...
The output should be something like the below:
Bidders | # of Bids |
ANT | 2 |
JAM | 3 |
NAT | 1 |
TOD | 2 |
UBT | 1 |
WBM | 3 |
YBA | 3 |
Thanks
Hi,
Try this
=DISTINCTCOUNT(Data[Bid])
Thanks for your response on both posts.
DistinctCount(Data[Bids]) won't work on the real data set because there are several instances where a Bidder will submit the same bid for different orders.
Is an adapation of the other solution not possible?
Hi,
I am not clear with your requirement. Share a sample dataset and show the expected result on that dataset.
I can't share actual datasets but I've copied an example below (I've made a few slight adjustments to my original example).
Order Number | Account | Bid ID | Product | Volume by Account | Volume by Order | Winner | Bidders | Bid |
1 | A | 50 | 12 | 500 | 500 | NAT | WBM | 235 |
1 | A | 50 | 12 | 500 | 500 | NAT | ANT | 235 |
1 | A | 50 | 12 | 500 | 500 | NAT | TOD | 235 |
1 | A | 50 | 12 | 500 | 500 | NAT | NAT | 233 |
2 | Z | 42 | 65 | 1,600 | 1,600 | JAM | YBA | 228 |
2 | Z | 42 | 65 | 1,600 | 1,600 | JAM | JAM | 225 |
3 | Z | 42 | 83 | 700 | 700 | UBT | UBT | 71 |
3 | Z | 42 | 83 | 700 | 700 | UBT | YBA | 73 |
4 | L | 28 | 12 | 8,000 | 106,000 | WBM | JAM | 225 |
4 | L | 28 | 12 | 8,000 | 106,000 | WBM | WBM | 210 |
4 | A | 51 | 12 | 5,000 | 106,000 | WBM | JAM | 225 |
4 | A | 51 | 12 | 5,000 | 106,000 | WBM | WBM | 210 |
4 | T | 13 | 12 | 28,000 | 106,000 | WBM | JAM | 225 |
4 | T | 13 | 12 | 28,000 | 106,000 | WBM | WBM | 210 |
4 | B | 14 | 12 | 5,000 | 106,000 | WBM | JAM | 225 |
4 | B | 14 | 12 | 5,000 | 106,000 | WBM | WBM | 210 |
4 | G | 96 | 12 | 50,000 | 106,000 | WBM | JAM | 225 |
4 | G | 96 | 12 | 50,000 | 106,000 | WBM | WBM | 210 |
4 | I | 73 | 12 | 10,000 | 106,000 | WBM | JAM | 225 |
4 | I | 73 | 12 | 10,000 | 106,000 | WBM | WBM | 210 |
5 | B | 15 | 58 | 800 | 800 | TOD | ANT | 87 |
5 | B | 15 | 58 | 800 | 800 | TOD | TOD | 86 |
6 | I | 74 | 79 | 800 | 800 | WBM | WBM | 398 |
6 | I | 74 | 79 | 800 | 800 | WBM | YBA | 405 |
7 | G | 96 | 38 | 2,250 | 8,500 | JAM | JAM | 200 |
7 | A | 52 | 38 | 1,750 | 8,500 | JAM | JAM | 200 |
7 | B | 16 | 38 | 4,500 | 8,500 | JAM | JAM | 200 |
The below is what I'm trying to accomplish. i.e a count of the number of times a Bidder has bid on a (unique) order.
ANT | 2 |
JAM | 3 |
NAT | 1 |
TOD | 2 |
UBT | 1 |
WBM | 3 |
YBA | 3 |
Hi,
Drag Biddes to the Row labels and use this measure
=SUMX(SUMMARIZE(VALUES(Data[Order Number]),[Order Number],"ABCD",DISTINCTCOUNT(Data[Bid])),[ABCD])
Hope this helps.
Thanks for the answer. It almost gets me to my final output, but not quite as I need both the Winners and Bidders information in the one table (and then the ability to calculate a % of Bids Won.
The final table I need the below. When I use Bidders in the Row labels it works, but then the Winners are wrong. When I use Winners in the Row labels the Winners are correct, but the Bidders are wrong.
I will actually have a separate query that is a unique list of Winners/Bidders that has relationships to the existing query.
Desired end result:
Supplier | Wins | Bids | % of Bids Won |
ANT | 0 | 2 | 0% |
JAM | 2 | 3 | 67% |
NAT | 1 | 1 | 100% |
TOD | 1 | 2 | 50% |
UBT | 1 | 1 | 100% |
WBM | 2 | 3 | 67% |
YBA | 0 | 3 | 0% |
Apologies for continually adding extra parts. I'm stepping through the process as I go
Hi,
You may download my solution from here.
Hope this helps.
Thanks @Zubair_Muhammad but this doesn't quite work over the full dataset.
If I have 2 orders with the same Winner that are also for the same volume, it only counts one.
I've built a table with what I want - using the "New Table" function under "Modeling" but I'm not sure how to translate this into a sum type measure in my original query (i.e. without actually creating the "New Table").
Table = ADDCOLUMNS(SUMMARIZE(Sheet1,Sheet1[Order Number]),"Volume",CALCULATE(VALUES(Sheet1[Volume by Order])),"Winner",CALCULATE(VALUES(Sheet1[Winner])))
Volume2 = SUM('Table'[Volume])
I also tried Volume = SUMX(FILTER(Sheet1,DISTINCT(Sheet1[Order Number])),Sheet1[Volume by Order]) but got the error that mutiple values were supplied where a single value was expected
Try this MEASURE
Volume = SUMX ( SUMMARIZE ( TableName, TableName[Winner], TableName[Volume by Order] ), CALCULATE ( VALUES ( TableName[Volume by Order] ) ) )
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 |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |