Forum Discussion

aarushirc14's avatar
aarushirc14
New Member
3 years ago
Solved

Group and sum distinct values

I need to group Initiatives together and calculate the ROI sum for each Parent Intiative. When calculating the ROI sum for a Parent Intiative, each unique Intiative (or unique ID) should only be included in the sum once. 

For example,

Parent Initiative: IG BI Data Pulls. So the Initiatives are IG BI Data Pulls Phase 1 and IG BI Data Pulls Phase 2. So the ROI sum is 16375+29275= 45650.

 

Currently I'm using the following DAX: 

PI ROI Yr 1 = SUMX(VALUES('ROI Table'[Parent Initiative]),CALCULATE(SUM('ROI Table'[ROI])))
 
But this expression does not sum ROI values of distinct Initiatives. When using my DAX  function, in the example I provided, the incorrectly calcualted ROI is 16375+16375+29275+29275= 91300.
 
What would be the correct DAX function? **Please note I'm not allowed to change the structure of my table (like removing duplicate Initiatives).

 

Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi aarushirc14 ,

     

    Here I suggest you to create a measure by below code.

     

    PI ROI Yr 1 = SUMX(SUMMARIZE('ROI Table','ROI Table'[Parent Intiative],'ROI Table'[ROI]),[ROI])

     

    Sample:

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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

     

4 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    3 years ago

    aarushirc14 

    i think you can create a new ROI column and use the new column to do the calculation

    Column = 'Table'[ROI]/CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[ID]))

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi aarushirc14 
    Please try

    PI ROI Yr 1 =
SUMX (
    VALUES ( 'ROI Table'[Parent Initiative] ),
    CALCULATE ( MAX ( 'ROI Table'[ROI] ) )
)
  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi aarushirc14 
    Please try

    PI ROI Yr 1 =
SUMX (
    VALUES ( 'ROI Table'[Parent Initiative] ),
    CALCULATE ( MAX ( 'ROI Table'[ROI] ) )
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi aarushirc14 ,

     

    Here I suggest you to create a measure by below code.

     

    PI ROI Yr 1 = SUMX(SUMMARIZE('ROI Table','ROI Table'[Parent Intiative],'ROI Table'[ROI]),[ROI])

     

    Sample:

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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