Average and Forecast

[edson_souza]

I have the table below:

Month Budget Realized
Oct $489943,13 $515707,20
Nov $883368,63 $643675,04
Dec $637668,28 $681661,99
Jan $1931667,93 $626560,46
Fev $1289529,29 $748939,90
Mar $2372920,47 $739028,26
Apr ($289462,65) $667915,29
May $759686,98 $920683,26
Jun $603624,00 $800318,55
Jul $633832,05 $697527,81
Aug $655151,28 $51019,12
Sep $556045,26 $0,00

I need two pieces of information:

1) average of realized = sum total realized / quantity of distinct months where sum realized of month> 0;
Which would be = 7,093,036.88 / 11 = 644,821.53

2) Forecast at closing = Sum of the realized + result of the application of the forecast function for the remaining months.
For example:
I have 10 months of execution and 2 is missing by the end of the year. So I have to make a prognosis of the others according to the ones I already have, that is, to make the projection for the others. In the table above, I have 11 months running and 1 is left, I need the projection of that last month according to what has already been done.

Can someone help me?

Tnks,
Edson.

[edson_souza · Accepted solution]

Solved in topic: https://community.powerbi.com/t5/Desktop/Average-and-Forecasting/td-p/238486

Tnks.

[Phil_Seamark]

Hi @edson_souza

I think this might be close to the correct answer for part 1 of your problem

Average of Realized = 
					CALCULATE(
						AVERAGE(
							'Table1'[Realised]
							),
							FILTER(
								'Table1',
								'Table1'[Realised]>0
								)
							)

However I'm not sure I understand part 2) fully,  are you able to provide what you think should be the correct answer for this part to help work out the inbetween plumbing 🙂

[edson_souza]

@Phil_Seamark, Thank you for your help.

For part 1, the expression you submitted did not resolve. It returned the average of the releases that make up the months. And I need the monthly average, so the division of the total realized by the amount of months that has been accomplished.

As for Part 2, I'll try to write it another way soon.

[Eric_Zhang]

---

@edson_souza wrote:

@Phil_Seamark, Thank you for your help.

For part 1, the expression you submitted did not resolve. It returned the average of the releases that make up the months. And I need the monthly average, so the division of the total realized by the amount of months that has been accomplished.

As for Part 2, I'll try to write it another way soon.

---

@edson_souza

Then for part 1, you can create a measure as below.

average_ =
CALCULATE (
    DIVIDE ( SUM ( Table1[Realized] ), COUNTROWS ( DISTINCT ( Table1[Month] ) ) ),
    FILTER ( Table1, Table1[Realized] > 0 )
)

As to part 2, please add more clarification.