Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 J...
Need Help
Show and Tell
  • jdbuchanan71's avatar
    jdbuchanan71
    7 years ago

    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.

jdbuchanan71's avatar
jdbuchanan71
Icon for Super User rankSuper User
7 years ago

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?

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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.