Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
millebg
Regular Visitor

Calculating a value by multiplying numbers in two columns of two different tables

I have a table with half hourly actuals for electricity produced, dating back to the start of the year. In addition to this i have corresponding half hourly forecasts in a separate table and then additional tables for a half hourly electricity price if within a tolerance level of the forecast and a different table with a different price if outside of that tolerance. I also have a date table broken down into half hourly periods (on the hour and half past the hour every day for the year). All tables are linked by the half hour period (i.e 18/07/2018 14:30:00) How can i create measures to calculate at the half hourly level and then correctly sum at a daily/monthly level? I want all revenue calculations done at a half hourly level (due to the possibility that within a half hour period it may be out of tolerance and therefore need to use the out of tolerance price, but the day as a total may be in tolerance and I want to therefore avoid the sum of the days output all being multiplied by the in tolerance price) and once half hourly revenue is calculated I can then sum to analyse at a daily and monthly revenue.

Thanks in advance.

3 REPLIES 3
Stachu
Community Champion
Community Champion

can you share examples of the tables involved and joins between them? otherwise it's difficult to propose relevant solution



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Power BI Relationships.pngPower BI RTable.png

 

Hi,

 

Relationships and example table are above. All tables are linked via a 1-1 relationship for the DateTime (i.e. 19/07/2018 14:30:00). Revenue needs to be calculated half hourly which then needs to be summed to give a total for the day and month. For the revenue, if the actuals are within a % threshold of the forecast, then the N2EX price for that 30 min period is used (also the same if below threshold). If, however, the actuals are above the threshold, the N2EX price is used for the portion within threshold and the Cashout Index is used for the portion over the threshold. I have a measure which correctly calculates it within the half hourly period, but this is not working at the total level (as the total actuals are within threshold of the total forecast, but there are individual half hour periods which are not, hence why I need a revenue calculation at half hourly level to then all sum to a total). My issue may arise from my revenue calculation using the SUMX function for actuals, forecast, N2EX price and SSP price. This works if displaying at a half hourly level, but at a total level it is obviously summing everything up (including prices) first, which I do not want to happen. I don't, however, know how else to get around the error of identifying a unique value from the column when writing the measure, without using SUMX.

 

 

 

 

Hi @millebg,

Please check the relationship both to single and check if it works fine.

Thanks,
Angelia

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.