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

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

    • edson_souza's avatar
      edson_souza
      Regular Visitor
      9 years ago

      Perfect, MFelix.

      Thank you very much for your help, see you soon.