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

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.

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    7 years ago

    Anonymous 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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      jdbuchanan71 

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

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

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