Budget vs. Actual Calculation

[zudar]

Hi all,

I'm having trouble figuring out how to turn an excel file into a PowerBI report.

This project is about calculating a settlement amount for a running (fiscal) year. Please click here to download the PBI file I prepared for this topic. The data model looks like this:

Stores:

Months:

Hours:

Budgets:

etc...

Rates:

Now the idea is that we compare, per region, 'the budgeted hours' versus 'the actual hours (and if not available the budget)'. So in essence, we need these two columns per region:

Let's look at Europe as an example. The total budget for 2022 is 540.000 hours. If we take the actualized amounts for the months that we have that actual data at our disposal, the total for 2022 is 534.886 hours. 

Now let's use the 'Rates' table to calculate what the settlement amount is expected to be in 2022.

For the budgeted amount: 540.000 hours belongs to the bandwidth of 500.000<->1.000.000 hours. In euro's, that's a: €5.540.000 base amount + 40.000*€11,50 = €6.000.000. This corresponds to an average rate of €6.000.000/540.000 = €11,11111.. per hour.

If we project this average rate onto the actualized amounts, we get: 534.886*€11,11111.. = €5.943.177,78.

For the actualized amount: 534.886 hours belongs to the bandwidth of 500.000<->1.000.000 hours. In euro's, that's: €5.540.000 base amount + 34.886*€11,50 = €5.941.189. This corresponds to an average rate of €5.941.189/534.886 = €11,10739.. per hour.

So for Europe: we have an expected settlement amount of  €5.941.189 - €5.943.177,78 = -€1.988,78.

I need this PowerBI Report to give me these numbers on a monthly basis for the current year (2022). I want to know these numbers per region, but I also want to know what the settlement amount is for the regions together. The calculations with the 'Rates' should always be done on a Region-level though.

I'm really struggling to come up with the right DAX calculations. I can build the excel file in minutes, but I have literally no idea how to do this in PowerBI/DAX and I find that really frustrating. I would really appreciate it if anyone can help me!

Thank you!

[v-cgao-msft · Accepted solution]

Hi @zudar ,

I will break down the calculation steps and hope it will help you. Still using Europe as an example, create these measures.

Europe Budget = CALCULATE(SUM(Budgets[Budget]),FILTER('Stores','Stores'[Region]="Europe"))

Europe_Hours or Budget = 
SUMX (
    SUMMARIZE (
        'Months',
        'Months'[Month],
        "Europe_Budget",
            CALCULATE (
                SUM ( Budgets[Budget] ),
                FILTER ( 'Stores', 'Stores'[Region] = "Europe" )
            ),
        "Europe_Hours",
            CALCULATE (
                SUM ( Hours[Hours] ),
                FILTER ( 'Stores', 'Stores'[Region] = "Europe" )
            ),
        "Hours (or Budget)",
            IF (
                CALCULATE (
                    SUM ( Hours[Hours] ),
                    FILTER ( 'Stores', 'Stores'[Region] = "Europe" )
                )
                    = BLANK (),
                CALCULATE (
                    SUM ( Budgets[Budget] ),
                    FILTER ( 'Stores', 'Stores'[Region] = "Europe" )
                ),
                CALCULATE (
                    SUM ( Hours[Hours] ),
                    FILTER ( 'Stores', 'Stores'[Region] = "Europe" )
                )
            )
    ),
    [Hours (or Budget)]
)

Average Rate_1 = 
VAR _rate =
    CALCULATE (
        MAX ( 'Rates'[Rate] ),
        FILTER (
            'Rates',
            [Europe Budget] >= 'Rates'[Lower Limit]
                && [Europe Budget] <= 'Rates'[Upper Limit]
        )
    )
VAR _base_amount =
    CALCULATE (
        MAX ( 'Rates'[Base Amount] ),
        FILTER (
            'Rates',
            [Europe Budget] >= 'Rates'[Lower Limit]
                && [Europe Budget] <= 'Rates'[Upper Limit]
        )
    )
VAR _bandwidth =
    CALCULATE (
        MAX ( 'Rates'[Bandwidth] ),
        FILTER (
            'Rates',
            [Europe Budget] >= 'Rates'[Lower Limit]
                && [Europe Budget] <= 'Rates'[Upper Limit]
        )
    )
VAR _budgeted_amount = _base_amount + ( [Europe Budget] - _bandwidth ) * _rate
RETURN
    DIVIDE ( _budgeted_amount, [Europe Budget] )

Actual Amount Average Ratio Projection = [Europe_Hours or Budget]*[Average Rate_1]

The Actualized Amount = 
VAR _rate =
    CALCULATE (
        MAX ( 'Rates'[Rate] ),
        FILTER (
            'Rates',
            [Europe_Hours or Budget] >= 'Rates'[Lower Limit]
                && [Europe_Hours or Budget] <= 'Rates'[Upper Limit]
        )
    )
VAR _base_amount =
    CALCULATE (
        MAX ( 'Rates'[Base Amount] ),
        FILTER (
            'Rates',
            [Europe_Hours or Budget] >= 'Rates'[Lower Limit]
                && [Europe_Hours or Budget] <= 'Rates'[Upper Limit]
        )
    )
VAR _bandwidth =
    CALCULATE (
        MAX ( 'Rates'[Bandwidth] ),
        FILTER (
            'Rates',
            [Europe_Hours or Budget] >= 'Rates'[Lower Limit]
                && [Europe_Hours or Budget] <= 'Rates'[Upper Limit]
        )
    )
VAR _budgeted_amount = _base_amount + ( [Europe_Hours or Budget] - _bandwidth ) * _rate
RETURN
    _budgeted_amount

Average Rate_2 = DIVIDE([The Actualized Amount],[Europe_Hours or Budget])

Expected settlement amount in Europe = [The Actualized Amount]-[Actual Amount Average Ratio Projection]

Attached PBIX file for reference.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[zudar]

That's amazing. Thank you!