cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper IV
Helper IV

Dax

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.
3 REPLIES 3
Memorable Member
Memorable Member

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

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Super User II
Super User II

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.