calculation with aggregated and not aggregated fields

[Giada90]

Hi,

I have an excel like this:

I need to calcolate the margin for each person and I need to make sales - sum(costs), the result must me something like this:

How can I calculate the margin?

Thanks

[Giada90]

@bhanu_gautam I tried like this 

Colonna = Foglio1[sales]-sum(Foglio1[cost])

but it doesn't work

any suggestion?

thanks

[bhanu_gautam · Accepted solution]

@Giada90 , I created calculated colmn using

TotalCost =

CALCULATE(

    SUM('Table'[Cost]),

    ALLEXCEPT('Table', 'Table'[Person])

)

And one for subtraction 

Margin = 'Table'[Sales]-'Table'[TotalCost]

Please find the attached PBIX

[Giada90]

thanks!!

[bhanu_gautam]

@Giada90 , Can you elaborate this , as of now it seems sales amount from sum of cost to get margin you can do this in Power Query creating new custom column or you can also do this at front end by creating a new calculated column