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" below you see that the measure is taking [points per pour] * [planned pours] to get [planned points]. Similarly in Screenshot 2 it is doing the same.

 

This issue I am having is that when I remove the [job_id] field from the visual I want to show total [planned points] by plant (i.e. a sum of the [planned points] by [job_id]) NOT total [points per pour] times total [planned pours]. Are there any dax functions that would help me aggregate the data in my [planned points] measure how I have described? Any help is appreeciated! Thank you!

 

Screenshot 1:

 

 

 

 

 

 

Screenshot 2:

Notice that the measure is working correctly to multiply [points per pour] and [planned pours]. However, I want to show the sum of the [planned points] per [job_id]. 

Need Help

6 Replies

  • 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.