Sum one column based on unique entries in another column

[Jordan1]

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

[Ashish_Mathur · Accepted 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.

[Jordan1]

This doesn't allow me to look at the volume for each Winner...

[Ashish_Mathur]

Hi @Jordan1,

I am not sure of whom you are replying to but my formula seems to work fine

[Jordan1]

@Ashish_Mathur

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)

[Ashish_Mathur]

Hi,

Are you using the same data as you have pasted above?  Share the link from where i can download your PBI file.

[Jordan1]

@Ashish_Mathur

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

[Ashish_Mathur]

Sure.  Let me know how it works.

[Jordan1]

@Ashish_Mathur

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:

1. A new table is created of the unique values of Order Number - VALUES(Data[Order Number])
2. I'm not quite sure what the SUMMARIZE function is doing. Would it matter if the MIN was changed to MAX - is this just used to get the Volume by Order data?
3. I don't really understand SUMX - I've Googled several explanations / examples but not quite sure what it's accomplishing
4. How does this data correctly relate to the Winner then without duplicating the volume for each line that the order number repeats?

I guess I struggle with the table / database logic in Power BI rather than the cell based focus in Excel.

Many thanks

[Ashish_Mathur]

You are welcome.

1. Correct
2. The SUMMARIZE function creates a virtual table with 2 columns - Order Number and the MIN value of each order number (the title of the MIN value column is ABCD - you may name it anything).  No it wont matter. You may use MAX, AVERAGE
3. SUMX function sums up the minimum values appearing in the ABCD column
4. Question not clear

[Jordan1]

@Ashish_Mathur

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

[Ashish_Mathur]

Hi,

Try this

=DISTINCTCOUNT(Data[Bid])

[Jordan1]

@Ashish_Mathur

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?

[Ashish_Mathur]

Hi,

I am not clear with your requirement.  Share a sample dataset and show the expected result on that dataset.

[Jordan1]

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

[Ashish_Mathur]

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.

[Jordan1]

@Ashish_Mathur

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

[Ashish_Mathur]

Hi,

You may download my solution from here.

Hope this helps.

[Jordan1]

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

[Zubair_Muhammad]

@Jordan1

Try this MEASURE

Volume =
SUMX (
    SUMMARIZE ( TableName, TableName[Winner], TableName[Volume by Order] ),
    CALCULATE ( VALUES ( TableName[Volume by Order] ) )
)

[Zubair_Muhammad]

@Jordan1