Forum Discussion

Yunxijiang's avatar
Yunxijiang
Regular Visitor
8 years ago
Solved

SUMX CALCULATION TOTAL

Hello,   I'm New to Power BI, Can you please help me with SUMX formula to calculate total value of a colone mesure ?   My data table in Excel is like Pic1, and the table in Powzer BI is like Pic2...
  • v-piga-msft's avatar
    v-piga-msft
    8 years ago

    Hi Yunxijiang，

     

    You could create the formulas like below.

    Measure = SUMX('Table1',[GAP_1])
Measure 2 = SUMX('Table1',[GAP_2])
Measure 3 = SUMX('Table1','Table1'[GAP_3])
Measure 4 = [Measure]+[Measure 2]+[Measure 3]

    Here is the result output.

     

    If you only need the measure 4, you also could modify the measure to Measure 5 = SUMX('Table1',[Measure])+SUMX('Table1',[Measure 2])+SUMX('Table1',[Measure 3])

     

    Hope this can help you!

     

    Best Regards,

    Cherry

v-piga-msft's avatar
v-piga-msft
Resident Rockstar
8 years ago

Hi Yunxijiang，

 

You could create the formulas like below.

Measure = SUMX('Table1',[GAP_1])
Measure 2 = SUMX('Table1',[GAP_2])
Measure 3 = SUMX('Table1','Table1'[GAP_3])
Measure 4 = [Measure]+[Measure 2]+[Measure 3]

Here is the result output.

 

If you only need the measure 4, you also could modify the measure to Measure 5 = SUMX('Table1',[Measure])+SUMX('Table1',[Measure 2])+SUMX('Table1',[Measure 3])

 

Hope this can help you!

 

Best Regards,

Cherry

  • Yunxijiang's avatar
    Yunxijiang
    Regular Visitor
    8 years ago
    Thank you very much Cherry!
    Best regards

    Yunxi
    • v-piga-msft's avatar
      v-piga-msft
      Resident Rockstar
      8 years ago

      Hi Yunxijiang,

       

      You're welcome! That's my pleasure!:smileywink:

       

      Best Regards,

      Cherry

      • Yunxijiang's avatar
        Yunxijiang
        Regular Visitor
        8 years ago

        Hello Cherry,

         

        I have a problem with SUMX again. I don't understand why it can't work anymore. I feel that the previous calculations were correct, and then it was wrong. 

         

        Here  it's a simple application about SUMX. As I said last time, I need a total of a colonne Gap. GAP m-1=ABS(Sales- VF). But you can see in the picture, it was wrong with GAP SUMX 1= SUMX( Table1; [Gap m-1]) : For exemple for Gap SUMX 1 It should be 149 instead of 164 for ITEM J00109.

         

         

         

        I revisited the answer you gave me before. I don't understand where the problem lies.
        For each line, Gap m-i should be equal to Gap SUMX 1 , the only difference is that the total value is different.  No?

         

        Thank you in advance !

        Best wishes

         

        Yunxi