Forum Discussion

Sinclair's avatar
Sinclair
Frequent Visitor
8 years ago

Calculate projection

Hi,

please for the following sample data, is it possible to calculate the cell's prevision by using the Month column as filter?

I'm not sure whether it is possible or not.

 

I want to calculate the cell's prevision of June, July and august (where the data are missing in the column "Cell")

 

For a selected period (for example from Feb. to June) in the filter (the column Month), 

the cell's prevision of June = [SUM(Cell)/SUM(Objective "where Cell is not null")]*(Objective of June)

 

Cell

Objective

Month

Year

5000

5500

1

2018

2546

3000

2

2018

3265

3000

3

2018

4587

4300

4

2018

1254

2000

5

2018

 

5600

6

2018

 

5200

7

2018

 

6000

8

2018

 

May I kindly ask for your help?

 

Thank you

Need Help

4 Replies

  • Sinclair's avatar
    Sinclair
    Frequent Visitor
    8 years ago

    Hi,

    please for the following sample data, is it possible to calculate the cell's prevision by using the Month column as filter?

    I'm not sure whether it is possible or not.

     

    I want to calculate the cell's prevision of June, July and august (where the data are missing in the column "Cell")

     

    For a selected period (for example from Feb. to June) in the filter (the column Month), 

    the cell's prevision of June = [SUM(Cell)/SUM(Objective "where Cell is not null")]*(Objective of June)

     

    Cell

    Objective

    Month

    Year

    5000

    5500

    1

    2018

    2546

    3000

    2

    2018

    3265

    3000

    3

    2018

    4587

    4300

    4

    2018

    1254

    2000

    5

    2018

     

    5600

    6

    2018

     

    5200

    7

    2018

     

    6000

    8

    2018

     

    May I kindly ask for your help?

     

    Thank you

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi Sinclair

     

    Giving high level idea: Create a calculated column New_Cell as shown below and display this column in visuals in the place of "Cell" column.

     

    New_Cell =

     

    VARIABLE inCalc = <Your calculation for Projection>

     

    IF ( ISBLANK(Cell) , inCalc, Cell)

     

    Try and let us know if this works.

     

    Thanks
    Raj

    • Sinclair's avatar
      Sinclair
      Frequent Visitor
      8 years ago

      Hi Anonymous

       

      thanks a lot for your proposition. I think it cannot be a calculated colomn because the value of the projection should be updated according to the Range selected in the filter.

      In the filter we can select for example 

       Jan - June: and the projection of June should be calculated with the data of Jan to May

      Feb - July: and the projection June and July be should be calculated with the data of Feb to May

      ...

      The point is to be able to select diferents Range of Mouth and calculate the projection according to the Range selected

       

      Thanks

      Sinclair

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Hi Sinclair,

         

        You can try to use below measure if it suitable for your requirement:

        Sample =
VAR currYear =
    MAX ( Table[Year] )
VAR monthList =
    ALLSELECTED ( Table[Month] )
VAR lastMonth =
    MAXX ( ALLSELECTED ( Table[Month] ), [Month] )
VAR divi =
    DIVIDE (
        SUM ( Table[Cell] ),
        CALCULATE (
            SUM ( Table[Objective] ),
            FILTER (
                ALL ( Table ),
                [Year] = currYear
                    && [Month] IN monthlist
                    && [cell] <> BLANK ()
            )
        ),
        -1
    )
RETURN
    divi * SUM ( Table[Objective] )

        Regards,

        Xiaoxin Sheng