Forum Discussion

Sergun70's avatar
Sergun70
Frequent Visitor
8 years ago

Allocate rest ammount

I have allocated planned budged over time proportionally  up to end date and i have fact column and know i don't know how to allocate differenece of planned budget and actua from last transactionl up to end date. Help will be very appreciated!

4 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    8 years ago

    Sergun70can you provide soem sample data and expected result it will help for better support

    • Sergun70's avatar
      Sergun70
      Frequent Visitor
      8 years ago
      My data looks like this: 
      Project budget:12000
         
       Budget equally allocatedActual
      Jan1000900
      Feb1000900
      Mar1000900
      Apr1000900
      May1000900
      Jun1000900
      Jul1000900
      Aug1000 
      Sep1000 
      Oct1000 
      Nov1000 
      Dec1000 
      Total120006300
         
      Desired result: 
      MonthBudget allocatedActual
      Jan1000900
      Feb1000900
      Mar1000900
      Apr1000900
      May1000900
      Jun1000900
      Jul1000900
      Aug10001140
      Sep10001140
      Oct10001140
      Nov10001140
      Dec10001140
      Total1200012000
      • v-yulgu-msft's avatar
        v-yulgu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        Hi Sergun70,

         

        If the [Month] column in dataset is sorted in correct order (Jan, Feb, Mar, Apr etc), please add an index column in Query Editor mode.

        If the [Month] column is sorted in irregular order, (like, Jan, Mar, Nov, etc), please add a conditional column in Query Editor to list MonthNo for each Month name.

         

        Then, in report view, create calculated column.

        Actual 2 =
IF (
    'Project budget'[Index] > MONTH ( TODAY () ),
    (
        CALCULATE (
            SUM ( 'Project budget'[Budget equally allocated] ),
            ALL ( 'Project budget' )
        )
            - CALCULATE (
                SUM ( 'Project budget'[Actual] ),
                FILTER ( 'Project budget', 'Project budget'[Index] <= MONTH ( TODAY () ) )
            )
    )
        / ( 12 - MONTH ( TODAY () ) ),
    'Project budget'[Actual]
)

         

        To get your desired output, you can create a calculated table as well.

        Result Table =
SELECTCOLUMNS (
    'Project budget',
    "Month", 'Project budget'[Month],
    "Budget allocated", 'Project budget'[Budget equally allocated],
    "Actual", 'Project budget'[Actual 2]
)

         

        Best regards,

        Yuliana Gu