Dax

[Anonymous]

Hola

Estoy tratando de crear una columna calculada

SUM(flow_cost_alignment[demand_monthly_cost_estimate])/sum(flow_cost_alignment[flujo]

pero quiero ver los valores a lo largo del tiempo. En este momento estoy viendo un solo valor para todas las fechas. Necesito ayuda con el DAX para poder obtener valores con el tiempo.

[andre]

Tratar

calculate(SUM(flow_cost_alignment[demand_monthly_cost_estimate]))/calculate(sum(flow_cost_alignment[flow])

puedes ver este video si quieres entender por qué SUM() no está funcionando de la manera que quieres que funcione

[TomMartens]

Hey @andre ,

I do not agree with both of the solutions you recommend in your video, either to wrap a CALCULATE() around the SUM() or using a measure inside a calculated column.

Both approaches are not recommended:

• CALCULATE(...)
  CALCULATE(...) even if it works in your example it just creates unnecessary overhead, even if the DAX expression will just be evaluated during data refresh, it also can return unexpected results. If you don't mind you might read my blog here: https://www.minceddata.info/process/dax/dax-foundation/ If you follow along you will find a pbix, this pbix contains two pages "context transition - unexpected result" and "context transition - unexpected result - why". Here I explain why CALCULATE might return an unexpected result (I'm using this pbix for presentation on conferences, no one who was brave enough to "estimate" the result was right on his/her 1st guess).
• Using the measure
  The measure works because referencing a measure always wraps (of course implicitly) a CALCULATE around itself, initiating a context transition. Another reason it's not recommended to use a measure, inside a calculated column. It's simply this, a measure always reflects, the interaction of the user with the data, meaning it will be recalculated on every user interaction. This will not happen if the measure is used inside a calculated column, as these columns will be only calculated during data refresh. Thinking in layers, the first layer of the Power BI data model will be created using Power Query, the 2nd layer is created by using DAX to create calculated columns, and the final layer is created by measures. Personally, I consider these layers a one-way passage.

If you just have time reading one article, read this article by Jeffrey Wang: http://mdxdax.blogspot.com/2011/03/logic-behind-magic-of-dax-cross-table.html Here Jeffrey explains the 4 steps that are happening if CALCULATE is used. This article also explains why SUM returns the same value for each row because SUM('t'[c]) is internally translated to SUMX('t', 't'[c]), where the table is referencing the filtered table. As we are creating a calculated column, there is no filter context, meaning the whole table, all the rows, is considered for aggregation. 

The most simple solution would be just to use the 't'[c], without using SUM or CALCULATE(SUM

Regards,

Tom

[TomMartens]

Hey @sbhambri ,

Tengo que admitir que no entiendo su requisito, lo que significa lo que quiere decir con "... ver los valores a lo largo del tiempo. ...". Tal vez sólo omita la SUMA. Si va a crear una columna calculada, no tiene que ajustar las referencias de columna en una función de agregado como SUM.

Si esto no es lo que está buscando, considere la posibilidad de crear un archivo pbix que contenga datos de ejemplo, pero que refleje el modelo de datos. Puede usar esto para escribir datos directamente en Power BI Desktop: https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-enter-data-directly-into-desktop. Cargue el archivo pbix en onedrive o Dropbox y comparta el enlace. Si está utilizando Excel para crear los datos de ejemplo, comparta el xlsx también.
Cree columnas que contengan los resultados esperados, use los datos de ejemplo para explicar cómo se deriva el resultado de los datos de ejemplo.

saludos

Tom