Forum Discussion
Row by Row calculation for a measure
Hi,
I have a tables Load and Generation which are connected to table Timeseries through datetime. One datetime in Timeseries has multiple datetime in 'Load',which I filter by site. Similarly one datetime in timeseries has many datetimes in 'Generation'.
I am trying to calculate the difference between Load[Load] and Generation[Generation] row by row before aggregating.
My first attempt is below and it works for the lowest drill down level but it aggregated and then subtracts. I need it to subtract and then aggregate.
Load_Generation_diff =
SUM ( Load[Load] ) - SUM ( Generation[Generation] )
Using the related function also doesn't work. likely due to the Many to one relationship
Load_Generation_diff =
SUMX ( 'Load', Load[Load] - RELATED ( Generation[Generation] ) )
5 Replies
- DOLEARY85
Resident Rockstar
Hi,
try wrapping the calculations in CALCULATE TO change the context to row calculation:
Load_Generation_diff =
CALCULATE(SUM ( Load[Load] )) - CALCULATE(SUM ( Generation[Generation] ))then aggregate this after
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
Unfortunately that doesn't solve the problem.
- DOLEARY85
You should get an idividual calculation per row that you can then sum.
What's happening when you use it?
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
