Forum Discussion

fbarry's avatar
fbarry
Frequent Visitor
6 years ago
Solved

SUMX issue

Hi All,   I'm having an issue trying to get a SUMX expression to return the expected results, and after many hours of toil I am now officially stumped.  Ultimately, am trying to create a measure t...
sumx
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    Please try this one.  It gets the same results on rows and correct total.  This is the more logical way I should have written it in the first place.

     

    New SUMX =
    SUMX (
    SUMMARIZE ( Invoice_items, Invoices[Inv_No], Invoice_items[Product] ),
    CALCULATE (
    DIVIDE ( SUM ( Receipts[Amount] ), SUM ( Invoices[Amount] ) )
    * SUM ( Invoice_items[Amount] )
    )
    )

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

I looked at your pbix.  Things get simpler if you use the Inv_No from your Invoices table in your table visual (instead of the one from the Inv_Items table).  If so, you just adapt the two measure below to get your desired result (I believe).

 

Inv % Paid = DIVIDE([Inv_Receipts], SUM(Invoices[Amount]))
 
SumX Measure = SUM(Invoice_items[Amount]) * [Inv % Paid]
 
Also, apply a Filter on this visual where Inv_Items[Amount] is not blank to get this result.
 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

fbarry's avatar
fbarry
Frequent Visitor
6 years ago

Hi mahoneypat ,

 

Thanks for your reply.

 

Your suggestion works for unfiltered data, but as soon as I apply a filter to this visualisation (filter by Product) the totals are incorrect. I need to be able to filter this by Product.

 

 

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    It seems to work when I filter by Product.  If you can further describe and send screen shot of what you are seeing, I can adapt it.

     

    Regards,

    Pat

     

    • fbarry's avatar
      fbarry
      Frequent Visitor
      6 years ago

      Hi mahoneypat,

       

      If I select a Product (product B in the below example), the rows on the table below will not tot up the the total value. It is actually the rows that are correct but the total is wrong. It is the same total as appearing on the Product B row in the upper table, but this is also wrong. 

      The reason for this (I believe) is that I'm using the [Inv % Paid] measure within my new measure, which calculates at invoice level and not invoice_items level and therefore if I just use a simple SUM it will not be an iterative calculation across each invoice when totalling. 

       

      Total of SumX Measure should be 5,283.20 as that is the sum of the rows. 

       

      • mahoneypat's avatar
        mahoneypat
        Icon for Microsoft Employee rankMicrosoft Employee
        6 years ago

        Please try this one.  It gets the same results on rows and correct total.  This is the more logical way I should have written it in the first place.

         

        New SUMX =
        SUMX (
        SUMMARIZE ( Invoice_items, Invoices[Inv_No], Invoice_items[Product] ),
        CALCULATE (
        DIVIDE ( SUM ( Receipts[Amount] ), SUM ( Invoices[Amount] ) )
        * SUM ( Invoice_items[Amount] )
        )
        )

         

        If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

        Regards,

        Pat