Calculating the Sum of Values in a column for unique values in another column

[Gdibbs]

Hello Community,

Looking for guidance on how to perform a calculation.  Providing here a simple example to reference my question against.

How would you write the expression to calculate the sums of values in one column tied to the unique value in another column.

Say for example I have Order #'s (reference image below) and I want to calculate the sum of the orders value for all the different lines items tied to that order.

In the attached image I have three orders (AAA, BBB, CCC) and each of these orders has different line items with values for those line items.  I am looking to write a measure in PBI to calculate the sum of the values tied to each unique order.

[Anonymous · Accepted solution]

Hi,Greg_Deckler .thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.
Hello,@Gdibbs .I am glad to help you.
Based on the information you've provided, I'm guessing your data is similar to this:

I have created simulated data which is automatically recognized in PowerBI as currency type data (numbers that can be calculated directly) where parenthesis are recognized as negative numbers.

If your real data meets this requirement, you can refer to my test below, if your real data does not meet this requirement, please let me know whether the two fields in your screenshot are measure or column, and if they are column, then what are their types?

Here is my test.

The measure I create:

M_1 =
CALCULATE (
    SUM ( 'Table'[FRT_CHRG_AMT_USD] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[SHIPMENT_NUMBER] = MAX ( 'Table'[SHIPMENT_NUMBER] )
    )
)

I also considered that if your data is of text type then you need to create a calculated column of numeric type first:

M_2 = 
CALCULATE (
    SUM ( Table_textNum[C_usd] ),
    FILTER (
        ALL ( 'Table_textNum' ),
        'Table_textNum'[SHIPMENT_NUMBER] = MAX ( 'Table_textNum'[SHIPMENT_NUMBER] )
    )
)

If you provide the data as a MEASURE instead of a different COLUMN (here's an example with measrue: [M_AMT_USD])

You can try the following measure.
[M_3]

M_3 = 
CALCULATE (
    SUMX ( 'Table', [M_AMT_USD] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[SHIPMENT_NUMBER] = MAX ( 'Table'[SHIPMENT_NUMBER] )
    )
)

If you can provide the pbix file without sensitive data, it helps to help you to solve the problem, you need to create the MEASURES which are suitable for your real computing environment, because the MEASURES will be affected by the computing environment, I hope my test results can give you good ideas.

https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fcommunity.powerbi.com%2Ft5%2FCommu...

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Gdibbs]

@Anonymous  Worked perfectly and thank you for the explanation.

@Greg_Deckler Thank you also for the support!

[Greg_Deckler]

@Gdibbs Place Order # in a Table visual along with this measure:

Measure = SUM( 'Table'[Order Value] )

[Gdibbs]

@Greg_Deckler  Thank you.  I need to rephrase my question for the outcome I am looking to get.  

The below is actual data.  What I am trying to get to is a measure that would provide me another column (when pulled into the table) that would show (or repeat the sum value) of the Shipment # 878785377 total freight_chrg_amt_usd.  Hence, it would show in the column a total value of $680.65 for each row of Shipment # 878785377.  (and of course all the other shipment #'s in the data set would show thier corresponding total freight charges.)

Hope this helps and I really appreciate the support.