Forum Discussion
SUMIFS Formula Replicate in Power BI Desktop
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?
Anonymous
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.
11 Replies
- jdbuchanan71
Super User
Hello Anonymous ,
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?
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
Anonymous 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.