topic Re: Sum values in Column C where the value in Column B matches value in Column A in DAX Commands and Tips

Sum values in Column C where the value in Column B matches value in Column A

duane1 — Wed, 18 Dec 2024 19:45:54 GMT

Hi.

In my table below, I would like to sum the values in "FROM_COUNT2" where the value in "FROM_PRODUCTID" matches the vaue in "TO_PRODUCTID". In this example, the TO_COUNT2 column should contain the sum of all the FROM_COUNT2 values where the TO_PRODUCTID matches the FROM_PRODUCTID. In excel, I would use SUMIF to do this easily. Sometimes, there is more than one value in the TO_PRODUCTID that matches the FROM_PRODUCTID.

This is in Power Pivot but I assume the same formula I would use in other power platforms like BI or query. Let me know if it should be posted elsewhere. Thanks.

Re: Sum values in Column C where the value in Column B matches value in Column A

lbendlin — Wed, 18 Dec 2024 23:11:16 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Sum values in Column C where the value in Column B matches value in Column A

Anonymous — Thu, 19 Dec 2024 06:24:35 GMT

Hi duane1 ,

Would you like to achieve the following results?

Please try to add a new column and use the code below.

TO_COUNT2 = CALCULATE( SUM(TEST[FROM_COUNT2]), FILTER( TEST, TEST[TO_PRODUCTID] = EARLIER(TEST[FROM_PRODUCTID]) ) )

Could you please provide example data or sample files here if you have any confused? We could offer you more help if we have information in detail. There is sensitive data that can be removed in advance. How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Thanks for your understanding. Your time and cooperation are much valued by us. We are looking forward to hearing from you to assist further.

Best regards,

Lucy Chen

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

If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

Re: Sum values in Column C where the value in Column B matches value in Column A

Bibiano_Geraldo — Thu, 19 Dec 2024 06:48:40 GMT

Hi duane1 ,

Create a new calculated column using this DAX:

TO_COUNT2 = CALCULATE( SUM(TableName[FROM_COUNT2]), FILTER( TableName, TableName[TO_PRODUCTID] = EARLIER(TableName[FROM_PRODUCTID]) ) )

Replace table and columns names with your owns

Re: Sum values in Column C where the value in Column B matches value in Column A

duane1 — Thu, 19 Dec 2024 15:51:37 GMT

Thanks, this worked. I had a feeling EARLIER was involved. I was learning about it. This is the solution.

Re: Sum values in Column C where the value in Column B matches value in Column A

duane1 — Thu, 19 Dec 2024 15:52:46 GMT

Thanks, this is what worked. I received the same help from another.

Re: Sum values in Column C where the value in Column B matches value in Column A

lbendlin — Thu, 19 Dec 2024 16:33:36 GMT

EARLIER is a legacy function, and not recommended. Use variables.