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

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
Icon for Super User rankSuper 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.