Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
Too complicated. We change the calculations to sql. Now its working Thank you
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |