Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago

DAX HELP

I'm using this formula to create this measure (that didn't work)
%ToT =
IF(
'Purchase Invoices'[Amount Invoiced Company Currency (PI)] = 0,
0,
'Purchase Invoices'[Amount Invoiced Company Currency (PI)] /
CALCULATE(
SUM('Purchase Orders'[AmountPurchasedCompanyCurrency]),
CROSSFILTER('Purchase Invoices'[D_Purchaseorder_SID], 'Purchase Orders'[D_Purchaseorder_SID], BOTH)
)
)

 

Need Help

5 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    1 year ago

    Hi Anonymous - It seems you're trying to calculate the percentage of the total invoiced amount in relation to the total purchased amount by using a cross-filtering relationship between the PO and PI  tables based on the D_PurchaseOrder_SID column.

     

    Can you modify the formaule as below:

     

    %ToT =
    IF (
    SUM('Purchase Invoices'[Amount Invoiced Company Currency (PI)]) = 0,
    0,
    DIVIDE(
    SUM('Purchase Invoices'[Amount Invoiced Company Currency (PI)]),
    CALCULATE(
    SUM('Purchase Orders'[AmountPurchasedCompanyCurrency]),
    CROSSFILTER('Purchase Invoices'[D_PurchaseOrder_SID], 'Purchase Orders'[D_PurchaseOrder_SID], BOTH)
    )
    )
    )

     

    I hope it works 

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      I tried you solution but didn't work.
      I even tried with TREATAS function

      %ToT =
      IF(
      SUM('Purchase Invoices'[Amount Invoiced Company Currency (PI)]) = 0,
      0,
      DIVIDE(
      SUM('Purchase Invoices'[Amount Invoiced Company Currency (PI)]),
      CALCULATE(
      SUM('Purchase Orders'[AmountPurchasedCompanyCurrency]),
      TREATAS(
      VALUES('Purchase Invoices'[D_PurchaseOrder_SID]),
      'Purchase Order'[D_PurchaseOrder_SID]
      )
      )
      )
      )

      but didn't wor either.

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi Anonymous 

         

        Thank you very much rajendraongole1 for your prompt reply.

         

        Pls try this:

         

         

         

        %ToT =
IF(
    SUM('Purchase Invoices'[Amount Invoiced Company Currency (PI)]) = 0,
    0,
    DIVIDE(
        SUM('Purchase Invoices'[Amount Invoiced Company Currency (PI)]),
        CALCULATE(
            SUM('Purchase Orders'[AmountPurchasedCompanyCurrency]),
            FILTER(
                'Purchase Orders',
                'Purchase Orders'[D_PurchaseOrder_SID] IN VALUES('Purchase Invoices'[D_PurchaseOrder_SID])
            )
        )
    )
)

         

         

         

        If you're still having problems, provide some dummy data and the desired outcome. It is best presented in the form of a table.

         

        Regards,

        Nono Chen

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.