SUMIFS Formula Replicate in Power BI Desktop

[chodrick]

Okay, I have struggled for 2 days trying to figure this out to no avail.  I am trying to replicate the below SUMIFS formula in Power BI Desktop:

=IF([@Invoice]="","",SUMIFS('Customer Posted Pmt Jnl'!AO:AO,'Customer Posted Pmt Jnl'!AJ:AJ,[@Invoice],'Customer Posted Pmt Jnl'!AW:AW,"CUSTOMER PMT"))

I want to pull the total dollar amount on invoices flagged for "customer pmt".  The data is in two different tables: 1) An Aging table that has the original invoices and also where I want the formula to reside and 2) A Customer Posted Pmt table that has the invoices with the payment types (i.e. CUSTOMER PMT).

Can someone please help me with this?

[jdbuchanan71]

Hello @chodrick ,

Can you give us an idea of how the model is layed out?  Are the aging table and payment table related to each other:

Or do you have a master customer table that sits over the Aging and Payments tables?

[chodrick]

@jdbuchanan71 

The Aging table and Payments table are not related. There is a Many to Many relationship based on the invoice which is why I need a SUMIFS formula based on the Payments table. The invoice can be repeated several times in the Payments table based on the type of payment and the date of the payment. I do not have a master customer table that sits over the Aging and Payments tables.

[jdbuchanan71]

@chodrick If you added a customer table over the top of your 2 fact tables that would be the way to go.  You should also have a date table in your model.  Having the top level dimension tables will help a ton with creating reports and is really how PowerBI is designed to work.

[chodrick]

@jdbuchanan71 

Apologies, but I am very new to Power BI.  How would adding a master customer table and date table help in obtaining the sum of a certain type of payment by invoice number on the Aging table?  That is what my original formula depicts above.  And what would I include on the master customer table?

[jdbuchanan71]

@chodrick Gotcha, no a customer table would not help with that.  Any chance you can share your model so I can see the data? Perhaps an example of the desired output from your excel file as well.

[chodrick]

@jdbuchanan71 

I don't see where I can attach files in here.  Would I just send you screenshots?

[jdbuchanan71]

You would need to load the file to a service like OneDrive or DropBox and share the link.

[chodrick]

@jdbuchanan71 

See link below.  I hope this works!

https://teaminc-my.sharepoint.com/:f:/r/personal/camille_hodrick_teaminc_com/Documents/Power%20BI%20...

---

@jdbuchanan71 wrote:

You would need to load the file to a service like OneDrive or DropBox and share the link.

---

[jdbuchanan71]

@chodrick Nope, I don't have access to your sharepoint which is probably the way your network security team wants it 

You can set up a personal dropbox account in just a couple minutes and use that. https://www.dropbox.com

[chodrick]

@jdbuchanan71 

https://www.dropbox.com/sh/ywp59d96wss4e41/AADmmAvGcl7eQf9BCw6qv0Fka?dl=0

[jdbuchanan71 · Accepted solution]

@chodrick 

I modified your file to have a tble of all the unique invoices then joined that to each of the fact tables.  Then we can use the master invoice table to sum amounts from the 4 detail tables.

https://www.dropbox.com/s/s7geja95r1dz1k4/Recoveries%20Report.pbix?dl=0

I also put in a measure to sum the total of the payment column in the [CustPmtJnl_Q2] and an additional measure to sum that amount for only [RECOVERY TYPE] = "CUSTOMER PMT"

Payment Amount = SUM ( CustPmtJnl_Q2[Payment] )

Customer Payment Amount = 
CALCULATE(
    [Payment Amount],
    KEEPFILTERS(CustPmtJnl_Q2[RECOVERY TYPE] = "CUSTOMER PMT")
)

Using the master invoice list and writing mesasures over the other tables will let you combine the data together how you need.

Now you will probably need a master customer table and to add the customer number to the invoice table.  This is where you would want to start working with your company IT / BI folks to help get you the data you need for your model.