Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

SumX in table

I'm trying to do something I thought would be simple, but it's not working.  

I have a column that involves bad products that need to be returned.   The column is "Line Items Qty to Return", based on the Item.  

 

I can get the Line Items Qty to Return column to show the correct value ONLY by selecting "do not summarize" in the fields settings....otherwise it shows the incorrect values  (4 in this case).  

 

I thought by using a SUMX function, I could get the correct row level quantity.....but it also says 4...when it should say 1.   

 

My goal, besides having the correct row-level quantity show up, is to also have a total at the bottom...which is not currently happening in the column that shows the correct quantity value.     So my goal, again, would be to have a column that shows the correct row-level value, and also a correct total at the bottom.  

 

SumX Qty to Return = SUMX ( Merge1, CALCULATE ( SUM ( Merge1[RMA Line Items Item Qty To Return])))

 

 

Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    Thanks for the additional info, but I still must not be understanding your scenario (the pic was zoomed in too much, so I couldn't see all the new info).  When I try that measure, I get what I think is the desired result.  See the pic below.

     

     

    Can you explain a little further?

    Regards,

    Pat

5 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      The additional detail I can add is that this is a very simple model.   All of the data is in one table. 

       

      I simply need the "Unit Cost" column to be multiplied by the "RMA...Qty to Return" column....and show the result, at the row level.   So the total showing up in this column below should be or 342.58  (171.29 * 2).    I don't know where the 4,393 value is coming from. 

       

      • mahoneypat's avatar
        mahoneypat
        Microsoft Employee
        6 years ago

        I think the Calculate is throwing off your measure.  If you are iterating over the one table, you can just use

         

        NewMeasure = Sumx(Merge1, Merge1[Unit Cost]*Merge1[RMALineItems...])

         

        That should give you the right Total too.

         

        If this works for you, please mark it as the solution.  Kudos are great too.  Please let me know if it doesn't or if any questions.
        Regards,
        Pat