Custom column to calculate the sum of a column based on a value of a field in the row

[Anonymous]

Hi,

I have an Invoice table which it's rows are providing the Invoice line details. That means for every invoice there are several rows which each of them shows a product included in the invoice. We call them Invoice lines. Each invoice has a unique code called Invoice No. and each Invoice No has several invoice lines. each invoice line has a invoice value.

I want to add a column to the table, to show the total value of the invoice in the same row as each invoice line. to do that I need to tell it to calculate the sum of Invoice line values from each row which has the Invoice no. in SQL to calculate it is as below:

Select Invoice_No , sum(Invoice_Value) Total_Invoice_Value 

from Invoice_table
Group by Invoice_No

;

But how can I do it in Power BI? Two questions:

1- I am thinking to do it in the dataset and add a column to the same table. How can I do that? Can you tell me the way I have to do it with the code required?

2- Since there are many rows in the table, my experiments here in Power BI, takes a very long time to show any results. Is it better to this in the dataflow or maybe at the Database side?

Thanks in advance

BR

[JTBusinessIntel · Accepted solution]

Hi Sarkani, 

I think I'm understanding you correctly, where you want the total for the invoice as a value in each line of the table, where every record has the same total value for an invoice, dependent on which invoice number is in each row. 

If that's the case, you can achieve that in a calculated column pretty easily with the following DAX. 

Invoice Total = 
var InvNo = [Invoice_No]

var Total = SUMX(FILTER(ALL('Table Name'), 'Table Name'[Invoice_No] = InvNo),[Invoice_Value])

return
Total

One note: If you attempt to show the  "Invoice Total"  column we just created, make sure to change the aggregation type of the column, or it will sum the total for every record of that  invoice in your model. 

As far as your second question is concerned, I don't know a way to do the iterator function in M (Power Query). If you're working with a large data model, the calculated column will likely be a bigger addition to the overall size of your data than performing before loading the data to the model.

I have found that even in my larger data models, the calculated columns that are this straight forward don't tend to take a ton of time to process. If your experiments are taking a long time, try filtering out some of the data you might not need, and limiting the size of the model altogether.  It could potentially help your performance. 

Hopefully this helps!

[Anonymous]

Perfect. Thanks a lot. I also did tried it with M at Power Query level and it did took a long long time for it to get through the data. It is better to do it with DAX.