Calculated column with summarize

[AleMacedo]

I'm trying to use summarize function to get Contrat Value for a Unit inside company building.

My contrats values are total per Building. What I need is take this total, divide per total number of employees who works in determinated building than multiply per employees of each Unit.

I'll have contract value for each unit (that's the way my director want to see cost per unit)

This is my calculated column:

Valor Total Contrato EndUA1 = 

var TabelaIntegranteEnd = summarize(filter(Integrante,Integrante[Situacao]="Ativo"),Integrante[IdEnderecoUnidadeLotado],"IntegranteEnd",count(Integrante[IdEnderecoUnidadeLotado]))

var TabelaIntegranteUA = summarize(filter(Integrante,Integrante[Situacao]="Ativo"),Integrante[IdUA],"IntegranteUA", count(Integrante[IdUA]))

var TabelaContratoTotalEnd = summarize(CGE_ControleDespesasADM,CGE_ControleDespesasADM[IdEndereço],CGE_ControleDespesasADM[Período],CGE_ControleDespesasADM[DESPESA],"ValorTotalEnd",AVERAGE(CGE_ControleDespesasADM[Valor por Endereço]))

return

divide(sumx(TabelaContratoTotalEnd,[ValorTotalEnd]),sumx(TabelaIntegranteEnd,[IntegranteEnd]))*sumx(TabelaIntegranteUA,[IntegranteUA])

When I test summarize into a separated table the values are ok, but in this column value is repeated for each line. (not what I want)

What I did wrong? I have to use average for total End Contract because data received is replicating this value for each IDUA, duplicating values.

[lbendlin · Accepted solution]

Why would you do this as a calculated column?  There seem to be a lot of SUMX involved as well...

Please provide sanitized sample data that fully covers your issue.

Please show the expected outcome based on the sample data you provided.

[AleMacedo]

Too complicated. We change the calculations to sql. Now its working Thank you