Forum Discussion

Cowelle's avatar
Cowelle
Regular Visitor
1 year ago

Recreating SUMIFS

Hi,

I am trying to create a report and link a series of data sets. Freight Invoices and Duty invoices are going to be the corner stones of this report as these are invoices we have actually paid, and the other tables are the data behind that (charge codes and amounts, purchase orders shipped, units receipted etc)

I have linked freight invoices on a one to many relationship to both shipping report and Invoice data master

I am trying to create a calculated column that will tell me how much each purchase order is worth as a percentage of all units shipped based on an "invoice and BoL" concatenation,

In excel this would be as follows:

the aim is to match purchase orders that have the same units shipped and receipted so we can query any short or over deliveries

 

the issue i am facing is no matter what calculations i use, the returned value is always just the gross invoice value

 

Here is my most recent attempt (Calcualted column in "shipping report table"):

the ouput is this:

Any and all tips and advice would be appreciated, I've been pulling my hair out for over a week with this one

Thanks!

Need Help
Tutorial Requests

4 Replies

  • _AAndrade's avatar
    _AAndrade
    Resident Rockstar
    1 year ago

    Hi Cowelle,

    Please try to change your VAR TotUnits to this:

    Calculate(
    SUM('Invoice Data Master'[Amount in invoiced raised Currency]),
    FILTER(
           ALL('Freight Invoices'),
           'Freight Invoices'[Invoice & Bol] = CurrentInvoice
    )
)
    • Cowelle's avatar
      Cowelle
      Regular Visitor
      1 year ago

      Hi,

      thanks for your suggestion, but the total units ("Quantity") is the units shipped on that particular PO
      your version would be using the invoice £ value

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    1 year ago

    Hi,

    This should be written as a measure.  Share the download link of the PBi file.  Clearly show the problem there.

    • Cowelle's avatar
      Cowelle
      Regular Visitor
      1 year ago

      Hi,
      due to the clients i work with i cannot share the file for confidentiallity, this is why i shared the redacted screensnips, what is it you would need to see to further advise?
      I have tried writing this as a measure, and this is the output