Group and sum distinct values

[aarushirc14]

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

[Anonymous · Accepted solution]

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.

[tamerj1]

Hi @aarushirc14 
Please try

PI ROI Yr 1 =
SUMX (
    VALUES ( 'ROI Table'[Parent Initiative] ),
    CALCULATE ( MAX ( 'ROI Table'[ROI] ) )
)

[tamerj1]

Hi @aarushirc14 
Please try

PI ROI Yr 1 =
SUMX (
    VALUES ( 'ROI Table'[Parent Initiative] ),
    CALCULATE ( MAX ( 'ROI Table'[ROI] ) )
)

[ryan_mayu]

@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]))