How to sum one column based on unique values in another column

[nsessa]

Hi, 

I have one table i am currently trying to work with in Power Bi. I have 2 columns, one is sales orders, the other is the zip code that the order went to. I am trying to figure out how I can find the total orders that have the same zip code associated to it. 

What would be the best way to do this?

[amitchandak]

@nsessa , You can create new column and new measure like given below

new column = sumx(filter(table, [zip] = earlier([zip])),[sales])

new measure = sumx(filter(allselected(table), [zip] = max([zip])),[sales])

[nsessa]

Hi, 

Thank you both for the suggested solutions but neither of them to seem to work. The issue I am having in the beginning is that both the zip code & order column were in text format. I have changed to a whole number but whenever I try to create a visual off of the measure or column, I get an error message saying it can't display.

[daxer-almighty]

@nsessa

Zip codes should be text. Even if the format is a number, these are not numbers that should have a number semantics - you don't perform typical number operations like addition on zip codes. So, they should be text.

Second, if you drag the zip code column to a visual (say, table), then this measure will give you the total of orders that are associated to this code:

[Total] = SUM( T[Order Amount] )

where T is the name of your table.

If your need is different and you want to be able to drag orders onto a visual and then get the total for all orders for all the visible zip codes in the current context, then your measure would be:

CALCULATE( [Total], ALL( T ), VALUES( T[ZipCode] )

[mahoneypat]

To get the total for the same zip code, you can use this measure expression

Total This Zipcode = CALCULATE(SUM(Table[Amount]), ALLEXCEPT(Table, Table[Zipcode]))

Replace with your actual table and column names.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[camargos88]

Hi @nsessa ,

Just drag both columns to a table visual for example and change the aggregation of the sales order to count.

So you have a total orders by zip code.

[nsessa]

Hi @camargos88 thank you for that advice. Is there any way to do this as a measure though?