Forum Discussion

edson_souza's avatar
edson_souza
Regular Visitor
9 years ago
Solved

Average and Forecasting

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...
Need Help
  • MFelix's avatar
    MFelix
    9 years ago

    Hi edson_souza,

     

    Create the following measures:

    Forecast_Total =
IF (
    SUM ( Average_Forecast[Realized] ) = 0;
    CALCULATE ( [Average realized]; ALLSELECTED ( Average_Forecast ) );
    SUM ( Average_Forecast[Realized] )
)

     

    Forecast =
IF (
    SUM ( Average_Forecast[Realized] ) = 0;
    CALCULATE ( [Average realized]; ALLSELECTED ( Average_Forecast ) );
    SUMX (
        SUMMARIZE (
            Average_Forecast;
            Average_Forecast[Month];
            "Forecasted"; Average_Forecast[Forecast_Total]
        );
        [Forecasted]
    )
)

    The first one is just auxiliary to calculate the total value for the year, then just add the Forecast to your table should give expected result.

     

     

    Regards,

    MFelix

edson_souza's avatar
edson_souza
Regular Visitor
9 years ago

Thank you, MFelix.

Item 1 solved with the formula that you sent, although the values were not equal monthly, the general average was correct (644.821,53).

 

For item 2, the forecast would be: when realized, the same as realized. When there is no, equal to the monthly average already found. According to the table below:

MFelix's avatar
MFelix
Super User
9 years ago
Hi edson_souza,

I didn't used the decimal places that's way the values are not exactly the same.

To make this change you want just use this.formula

FORECASTED = IF ( SUM(TABLE[REALIZED])= 0; [AVERAGE_REALIZED]; SUM( TABLE[REALIZED]))

Use the previous measure in your 2nd argument in your formula.

Regards
MFelix
  • edson_souza's avatar
    edson_souza
    Regular Visitor
    9 years ago

    Hi MFelix

     

    I tested it this way, but the unrealized month continues with a value equal to 0.00.

    I noticed in your table "Average realized" has value in every month, mine only where realized.

    • edson_souza's avatar
      edson_souza
      Regular Visitor
      9 years ago

      I was able to apply the monthly average, but when I put the IF (Realized = 0; Average; Realized) the TOTAL does not add all the values.

      How can I do it?

      Note that the Forecast total is the same as Realized, but should also add up to 644,821.53, with the result equal to 7,737,858.41.

      • MFelix's avatar
        MFelix
        Super User
        9 years ago

        Hi edson_souza,

         

        Create the following measures:

        Forecast_Total =
IF (
    SUM ( Average_Forecast[Realized] ) = 0;
    CALCULATE ( [Average realized]; ALLSELECTED ( Average_Forecast ) );
    SUM ( Average_Forecast[Realized] )
)

         

        Forecast =
IF (
    SUM ( Average_Forecast[Realized] ) = 0;
    CALCULATE ( [Average realized]; ALLSELECTED ( Average_Forecast ) );
    SUMX (
        SUMMARIZE (
            Average_Forecast;
            Average_Forecast[Month];
            "Forecasted"; Average_Forecast[Forecast_Total]
        );
        [Forecasted]
    )
)

        The first one is just auxiliary to calculate the total value for the year, then just add the Forecast to your table should give expected result.

         

         

        Regards,

        MFelix