Forum Discussion

chenko90's avatar
chenko90
Frequent Visitor
4 years ago
Solved

Summing on multiple rows

Hi,

I am trying to create a measure that will sum my values based on two rows. My current formula is: 

SUMX(VALUES(Table[customer]),Table[Price Variance]) 

What i actually need is for the measure to sum the price variance based on the product within each customer. If I put produt in the SUMX formula then it disregards the customer and if i put customer then it doesn't take into account the product. Is there a way to tell it to consider both? 

 

Below is kind of what i am getting. The product numbers are right but then it summarizes it on customer so that one is wrong. I need it to say $20. 

Customer Name     $30

       Product A         $10

       Product B         $10

 

Hope this makes sense.

sum
SUMMARIZE
sumx
  • amitchandak's avatar
    amitchandak
    4 years ago

    chenko90 , This only need when Price Variance is measure

     

    SUMX(summarize(Table, Table[Product], Table[customer]), "_1", Table[Price Variance]), [_1]) 

     

     

    when price variance is a column

    SUMX(summarize(Table, Table[Product], Table[customer], Table[Price Variance]), [Price Variance])

     

    If this does not help
    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

5 Replies

  • ValtteriN's avatar
    ValtteriN
    Community Champion
    4 years ago

    Hi,

    Typically a simple SUM should work in these kinds of cases, but I assume there is some issue with using it. Try adding DISTINCT before your VALUES so something like this: 

    DISTINCT(VALUES(Table[customer]). It is somewhat difficult to understand what is going on here. Do you have some sort of sample data?

    I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

    My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/
    • chenko90's avatar
      chenko90
      Frequent Visitor
      4 years ago

      Here is a visual of what im working with. As you can see the total for customer A is wrong. The sku level numbers are correct. When i use the SUM formula it has the same issue where it runs the formula in the measure instead of summing the sku level numbers. 

       

      • amitchandak's avatar
        amitchandak
        Super User
        4 years ago

        chenko90 , This only need when Price Variance is measure

         

        SUMX(summarize(Table, Table[Product], Table[customer]), "_1", Table[Price Variance]), [_1]) 

         

         

        when price variance is a column

        SUMX(summarize(Table, Table[Product], Table[customer], Table[Price Variance]), [Price Variance])

         

        If this does not help
        Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  chenko90 ,

    I created some data:

    Here are the steps you can follow：

    Method one：

    Create measure.

    method one =
var _1=SUMMARIZE('Table','Table'[MAJOR_NAME],'Table'[SKU],'Table'[Price Vanriance])
return
SUMX(_1,[Price Vanriance])

    Result:

    Second method ：

    Create measure.

    second method_1 =
IF(ISINSCOPE('Table'[MAJOR_NAME])&& NOT(ISINSCOPE('Table'[SKU])),SUMX('Table',[Price Vanriance]),MAX('Table'[Price Vanriance]))
    second method_2 =
var _table=SUMMARIZE('Table','Table'[MAJOR_NAME],"_value",[second method_1])
return
IF(HASONEVALUE('Table'[MAJOR_NAME]),[second method_1],SUMX( _table,[_value]))

    Result：

     

    Best Regards,

    Liu Yang

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