Show non summarised cost

[andrew260z]

Hi all, been trying to create either a group by or a distinct sum to represent savings for some data

Example of the data below - the sum of the final cost is 21,000 but i need to also show the 7,000  (as the consolidatedob coumn would be distinct) and the actual amount paid was 7,000;

I've tried a few different ways to do this but it keeps showing me the summarised sum 

FinalCostSum = SUMX(Distinct('TrackingList Archive'[ConsolidatedOB]),SUM('TrackingList Archive'[Final_Cost]))

Should I be doing this with a group by perhaps and linking back to the value CsondolidatedOB?

[v-yangliu-msft · Accepted solution]

Hi  @andrew260z ,

I created the data:

Here are the steps you can follow：

1. Create measure.

HASONEVALUE =
IF(HASONEVALUE('Table'[date]),MAX('Table'[Final_Cost]),MAX('Table'[Final_Cost]))

2. Result:

Best Regards,

Liu Yang

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

[amitchandak]

@andrew260z , calculate is must in sumx function in case you are not using a measure

FinalCostSum = SUMX(values('TrackingList Archive'[ConsolidatedOB]),calculate(SUM('TrackingList Archive'[Final_Cost])))

[andrew260z]

Thanks for the suggestion but even with the VALUES instead it returns the sum of all 3 final_cost values (21,000) instead of the 7,000 (value of the final_cost in distinct)