Multi-step calculation to create factor that is multiplied against a future date; table output.

[iDataDrew]

Let me first briefly explain what I've done in Excel utilizing tables and aggregate functions (no PowerPivot at this point).

I have calculated out a factor/multiplier to be utilized in determining an electric load schedule based on a forecast from the overall grid (on a utility basis).  I get to this factor by first figuring out what percentage our electric load is to the utility's total load, and I do this on a weekday and hourly basis for each utility (see screen capture below).

I calculate out the above for a range of several weeks in the past.  Then I take the average of the above on a weekday and hourly basis for each utility (see screen capture below).  This becomes the factor/multiplier.

The PJM grid, which essentially manages the utility loads, posts a 7-day forecast for each utility.  I use these factors/multipliers against the forecasts to assist in determining our electric load schedule for each utility.  So the factor, which is an average of the percent of our load to the utility load from the past few weeks, is used against the forecasted loads to come up with an electric load schedule.

I'm trying to simplify these steps and the creation of multiple tables by incorporating them into one or two measures in Power BI.

I've tried usign SUMMARIZE() and CALCULATETABLE() and FILTER() in different ways but haven't had much success yet.  Something always goes wrong.  Any thoughts?

Thank you.

[v-sihou-msft]

@iDataDrew

Based on your description, I still can't understand your scenario.

What are those columns stand for? Which columns are involved in your calculation? What's your expected result? Can you clarify your requirement or simplify your sample?

Regards,

[iDataDrew]

@v-sihou-msft thanks for the reply.  I understand it's a bit confusing.  I tried to be somewhat thorough in my explanation though.  The column headers are electric utilities, but that doesn't matter much when it comes to understanding the logic itself in performing the calculation.  Basically, I need to average historical data to come up with factors/multipliers that will then be used to multiply against forecast values.  The results of multiplying the factor against the forecasts will be my output (in a table format).  I'm thinking I may have to use CALCULATETABLE() in some way.

I'm not sure if this clarifies things, but I'm not sure how else to put it.