How do I sum all special charges by invoice from table "invoicedtl" and show it as part of my query?

[croberts21]

I'm not great at PBI but I know some basics. I'm not great at DAX. I use PBI Desktop from July 2024 on Windows Server 2019.

We can have one job, and multiple invoices for that job stored on the "invoice" table. But a special charge call it "specialcharge", is on another table called "invoicedtl" with "chargetype=1". I've looked into SUMX using a FILTER but it doesn't make sense to me. 

I'll summarize the table layouts.

Invoice table fields: 
Invoicenum
invoicedate

Invoicedtl table fields: 
Invoice (connects to invoice.invoicenum)
chargetype (sum only chargetype=1)
price

For each invoice on the "invoice" table, I want to store the sum of all charges of chargetype=1. How do I do that?

Example data:

Invoice table: 
invoice invoicedate
1001 1/1/24
1002 1/2/24
1003 1/3/24
1004 1/4/24
1005 1/5/24

Invoicedtl table:

invoice chargetype price
1001 1 100
1001 1 110
1002 1 90
1003 1 70
1003 1 80


Results should be:

Invoice Invoicedate total_price
1001 1/1/24 210
1002 1/2/24 90
1003 1/3/24 150
1004 1/4/24
1005 1/5/24

Thank you. 

[saud968]

create a DAX measure in Power BI to calculate the total price for chargetype=1 for each invoice. Here’s how you can do it:

Load your data into Power BI:

Import both the Invoice and Invoicedtl tables.
Create a relationship:

Ensure there’s a relationship between Invoice[invoicenum] and Invoicedtl[Invoice].
Create a new measure:

Go to the Modeling tab and click on New Measure.

Enter the following DAX formula for the measure:

Total Special Charge =
CALCULATE(
SUM(Invoicedtl[price]),
Invoicedtl[chargetype] = 1
)

Add the measure to your table:

Create a table visual.
Add Invoice[invoicenum], Invoice[invoicedate], and the new measure Total Special Charge to the table.
Example DAX Measure
Here’s the DAX measure step-by-step:

Total Special Charge =
CALCULATE(
SUM(Invoicedtl[price]),
Invoicedtl[chargetype] = 1
)
Expected Output
With the measure in place, your table should look like this:

Table

Invoice Invoicedate Total Special Charge
1001 1/1/24 210
1002 1/2/24 90
1003 1/3/24 150
1004 1/4/24
1005 1/5/24
Explanation
CALCULATE: This function changes the context in which the data is evaluated.
SUM: This function sums up the price column.
FILTER: The filter Invoicedtl[chargetype] = 1 ensures only rows with chargetype=1 are considered.

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

[Kedar_Pande]

measure:

Total_Price_Measure = 
SUMX(
FILTER(
'invoicedtl',
'invoicedtl'[chargetype] = 1
),
'invoicedtl'[price]
)

In your report, you can now use this measure to display the total price per invoice.

If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[croberts21]

I created a measure for this and I get a blank field. I also cannot drag this field to the filter pane. Should I have created this field as a column instead? 

[Anonymous · Accepted solution]

Hi @croberts21 ,

All of the above solutions are feasible.

I made a .pbix as an attachment that you can download to see the details.

My measure is the same as Kuquistado's.

total_price = CALCULATE(SUM('Invoicedtl table'[price]),FILTER('Invoicedtl table',[chargetype]=1))

Note that you need to open "Show items with no data" and that there is a relationship between the two tables.

Best Regards,

Stephen Tao

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

[Cookistador]

Hello, 

I made a small test
Something like:
Total charge =
CALCULATE(
SUM(invoicedtl[price]),
invoicedtl[chargetype] = 1
)

Then you create a visual in which you add Invoice, Invoicedate and Total charge, and you should have the desired result 🙂
Keep inform if you need more details