Dax

[Anonymous]

Hi,

I am trying to create a calculated column 

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

but i want to see the values over time. Right now I'm seeing only single value for all the dates. I need help with the DAX so I can get values over time.

[andre]

try

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

you can watch this video if you would like to understand why SUM() is not working the way you want it to work

[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 @Anonymous ,

I have to admit that I do not understand your requirement, meaning what you mean by "... see the values over time. ...". Maybe just omit the SUM. If you are creating a calculated column you do not have to wrap column references into an aggregate function like SUM.

If this is not what you are looking for, consider to create a pbix file that contains sample data, but still reflects the data model. You can use this to enter data directly in Power BI Desktop: https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-enter-data-directly-into-desktop. Upload the pbix file to onedrive or dropbox and share the link. If you are using Excel to create the sample data, share the xlsx as well.
Create columns that contain the expected results, use the sample data to explain how the result is derived from the sample data.

Regards,

Tom