Forum Discussion

jyeager's avatar
jyeager
Helper I
6 years ago

Help Needed! Data Aggregation / Summing Two Measures

Hello All! I am having some trouble with a measure I've named "planned points". The measure is simply multiplying [points per pour] and [planned pours]. It actually works as written. In "Screenshot 1...
Need Help
zoloturu's avatar
zoloturu
Memorable Member
6 years ago

Hi jyeager ,

 

If job_id is a grain of data then you can simply use SUMX:

 

= SUMX( 'YourTable', [points per hour] * [planned hours])

 
Regards,
Ruslan Zolotukhin (zoloturu)
BI Engineer at Akvelon Inc. / Kharkiv Power BI User Group Leader / DAX & Power BI Trainer
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!
It was useful? Press Thumbs Up!

You are from Ukraine? If yes then welcome to Power BI User Group - KhPUG website. Other country? Check and find proper one - Power BI User Groups

  • jyeager's avatar
    jyeager
    Helper I
    6 years ago

    Hi zoloturu 

     

    Thanks for the quick response! SUMX did not work. Its worth noting that the [points per pour] and [planned pours] fields are measure themselves:

    • [points per pour] = CALCULATE(SUM('database-pbi'[value]),'datbase-pbi'[category]="points per pour")
    • [planned pours] = CALCULATE(SUM('database-pbi'[value]),'datbase-pbi'[category]="planned pours")

     

    Here is the result when I tried SUMX (measure was titled "NEW planned points":

     

     

     

     

    Any other thoughts/suggestions? Thanks again!

     

    JY

    • amitchandak's avatar
      amitchandak
      Super User
      6 years ago

      First create two columns in table

      table column 1 = if(datbase-pbi'[category]="planned pours" ,'database-pbi'[value]),blank)

      table column 2= if(datbase-pbi'[category]="points per pour" ,'database-pbi'[value]),blank)

       

      You can multiple in 3rd column or use sumx.

       

       

    • zoloturu's avatar
      zoloturu
      Memorable Member
      6 years ago

      Hi jyeager ,

       

      You can still calculate it in a measure. I did similar many times:

      new planned points = 
VAR __CurrentRowMonth = MAX('Date'[Month])
VAR __CurrentRowPlant = MAX('...'[plant])
VAR __TempTable = SUMMARIZE('database-pbi','Date'[Month],'...'[plant],"planned points",[points per hour]*[planned pours])
VAR __TempTable2 = FILTER(__TempTable,[Month] = __CurrentRowMonth, [plant] = __CurrentRowPlant)

RETURN
    SUMX(__TempTable2, [planned points])

      Just correct table names in quotes.


      Regards,
      Ruslan Zolotukhin (zoloturu)
      BI Engineer at Akvelon Inc. / Kharkiv Power BI User Group Leader / DAX & Power BI Trainer
      -------------------------------------------------------------------
      Did I answer your question? Mark my post as a solution!
      It was useful? Press Thumbs Up!

      You are from Ukraine? If yes then welcome to Power BI User Group - KhPUG website. Other country? Check and find proper one - Power BI User Groups

      • jyeager's avatar
        jyeager
        Helper I
        6 years ago

        Thanks amitchandak  and zoloturu 

         

        I am getting blanks in the created column [planned points] and I think it is becuase the way the table is set-up. Below you will see that I a values column and a category column. It makes sense that when I just try to multiple the two created columns ([planned pours] and [points per pour]) I get a blank for my result ([planned points]). Any other thoughts here? It's like I need to place filters on the sum statements so that it is multiplying [month]&[job_id] of [planned pours] and [month]&[job_id] of [points per pour].... I think that can only be done in a measure though.