Row by Row calculation for a measure

[elegant_cow]

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] ) )

[DOLEARY85]

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 👍

[elegant_cow]

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 👍

[elegant_cow]

I am sorry, I think I misunderstood the real problem.

I can calulated the 'Load_Generation_diff' per row now (as per your forumla) and I am trying to calculate another measure from it. This measure is called 'Generation Sold'

Generation Sold = IF( [Load_Gen_diff] < 0, ABS([Load_Gen_diff]) ,0)

My data is hourly so at the hourly level it all works (and it worked before as well)

but as soon as I aggregate up (from hourly to daily), the 'Generation sold' doesnt show anything. I thought this was being 'Load_Generation_diff was no longer calculating per row (even with the new formula).

Sorry for not explaining the problem properly

[DOLEARY85]

Ah okay, i'm not sure without knowing the data model and fields - Are you able to share the PBIX file? or some sample data?

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍