cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jordan1
Frequent Visitor

Sum one column based on unique entries in another column

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

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

 

Try this calculated field formula

 

=SUMX(SUMMARIZE(VALUES(Data[Order Number]),[Order Number],"ABCD",MIN(Data[Volume by Order])),[ABCD])

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

20 REPLIES 20
Ashish_Mathur
Super User
Super User

Hi,

 

Try this calculated field formula

 

=SUMX(SUMMARIZE(VALUES(Data[Order Number]),[Order Number],"ABCD",MIN(Data[Volume by Order])),[ABCD])

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

image.png

 

 

 

Hi @Jordan1,

 

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

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@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)

image.png

 

 

 

 

 

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@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

Sure.  Let me know how it works.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@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

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Jordan1
Frequent Visitor

@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
ANT2
JAM3
NAT1
TOD2
UBT1
WBM3
YBA3

 

Thanks

Hi,

 

Try this

 

=DISTINCTCOUNT(Data[Bid])


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@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?

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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 NumberAccountBid IDProductVolume by AccountVolume by OrderWinnerBiddersBid
1A5012               500                 500NATWBM235
1A5012               500                 500NATANT235
1A5012               500                 500NATTOD235
1A5012               500                 500NATNAT233
2Z4265           1,600             1,600JAMYBA228
2Z4265           1,600             1,600JAMJAM225
3Z4283               700                 700UBTUBT71
3Z4283               700                 700UBTYBA73
4L2812           8,000         106,000WBMJAM225
4L2812           8,000         106,000WBMWBM210
4A5112           5,000         106,000WBMJAM225
4A5112           5,000         106,000WBMWBM210
4T1312         28,000         106,000WBMJAM225
4T1312         28,000         106,000WBMWBM210
4B1412           5,000         106,000WBMJAM225
4B1412           5,000         106,000WBMWBM210
4G9612         50,000         106,000WBMJAM225
4G9612         50,000         106,000WBMWBM210
4I7312         10,000         106,000WBMJAM225
4I7312         10,000         106,000WBMWBM210
5B1558               800                 800TODANT87
5B1558               800                 800TODTOD86
6I7479               800                 800WBMWBM398
6I7479               800                 800WBMYBA405
7G9638           2,250             8,500JAMJAM200
7A5238           1,750             8,500JAMJAM200
7B1638           4,500             8,500JAMJAM200

 

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.

 

ANT2
JAM3
NAT1
TOD2
UBT1
WBM3
YBA3

 

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@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:

SupplierWinsBids% of Bids Won
ANT020%
JAM2367%
NAT11100%
TOD1250%
UBT11100%
WBM2367%
YBA030%

 

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.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Jordan1
Frequent Visitor

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])

 

 

image.png

 image.png

 

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
Super User
Super User

@Jordan1

 

Try this MEASURE

 

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

Regards
Zubair

Please try my custom visuals

@Jordan1

 

sumx.png


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors