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

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.