Running total of Budget less Actual for each department

[joannerw]

I am trying to insert a column for the budget less actual for each department. i've attached a mock up table to show how my spreadsheet is set up. I would like to know, for each department, what the runnign total is for budget less actual, or am option to suggestions for alternatives . I'm fairly new to BI as well...

[v-juanli-msft · Accepted solution]

Hi @joannerw 

Agreed with amitchandak, you could create calculated columns as him suggested.

Monthsort = FORMAT([Month],"YYYY-MM")

You could also create measures if you want the final results to be dynamically with the slicers.

running budget =
CALCULATE (
    SUM ( 'Table'[Budget] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Dept]
            = MAX ( 'Table'[Dept] )
            && 'Table'[Monthsort]
                <= MAX ( 'Table'[Monthsort] )
    )
)

running actual = 
CALCULATE (
    SUM ( 'Table'[Actual] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Dept]
            = MAX ( 'Table'[Dept] )
            && 'Table'[Monthsort]
                <= MAX ( 'Table'[Monthsort] )
    )
)

If "less" from "runnign total is for budget less actual" means "minus",

then you could create measures

running budget-actual = 
CALCULATE (
    SUM ( 'Table'[Budget] )-SUM('Table'[Actual]),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Dept]
            = MAX ( 'Table'[Dept] )
            && 'Table'[Monthsort]
                <= MAX ( 'Table'[Monthsort] )
    )
)

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

Try this. I have not tested this. But you need to have month start or month-end date. Or Month in YYYYMM format so that is can be compared with last month

Cumm Budget =
Sumx(
filter('Table','Table'[Dept]=earlier('Table'[Dept]) && 'Table'[Month Date]<=earlier('Table'[Month Date])),'Table'[Budget])

Cumm Actual=
Sumx(
filter('Table','Table'[Dept]=earlier('Table'[Dept]) && 'Table'[Month Date]<=earlier('Table'[Month Date])),'Table'[Actual])

 Month Sort = format(table[date],"YYYYMM")

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin