cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Anonymous
Not applicable

## 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

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

Super User

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

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

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

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

Top Solution Authors
Top Kudoed Authors