Forum Discussion
SUMIFS Formula Replicate in Power BI Desktop
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.
See link below. I hope this works!
jdbuchanan71 wrote:You would need to load the file to a service like OneDrive or DropBox and share the link.
Super UserAnonymous Nope, I don't have access to your sharepoint which is probably the way your network security team wants it :smileyhappy:
You can set up a personal dropbox account in just a couple minutes and use that. https://www.dropbox.com
- jdbuchanan71
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.