topic Re: How to sum one column based on unique values in another column in DAX Commands and Tips

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

nsessa — Thu, 27 Aug 2020 15:30:42 GMT

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?

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

camargos88 — Thu, 27 Aug 2020 15:32:23 GMT

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.

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

nsessa — Thu, 27 Aug 2020 15:47:56 GMT

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

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

mahoneypat — Thu, 27 Aug 2020 16:06:01 GMT

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

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

amitchandak — Thu, 27 Aug 2020 16:27:29 GMT

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

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

nsessa — Sat, 29 Aug 2020 16:10:09 GMT

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.

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

daxer-almighty — Sun, 30 Aug 2020 07:11:13 GMT

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