Complex Tier Calculations with Nested If Statements

[Anonymous]

Hey y'all, I am having a hard time doing something that I did in Excel in Power Bi. I am esimating utility costs for different building types. I have the building type defined along with its annual electricity use and its peak demand. For the $$ calculation, there are three different schedules and each buildings falls under a schedule depending on its daily electricity consumption and its peak demand. So for example, if daily consumption is <15,000 kWh and demand < 50 kW, you fall within scenario 1. If daily consumption is >15,000 kWh and demand < 50 kW, you fall within scenario 2. If demand > 50 kW, you fall withing scenario 3. Then, under each scenario, there are different rates applied to daily consumption and to the demand, and these charges are often done in a tiered manner. So for example, in scenario 2, daily consumption from 0-15,000 kWh is charged at $0.5, and from 15,000-30,000 at $0.4 and so on. In the same scenario 2, peak demand from 0-30 kW is charged at $5 and from 31-50 kW, at $7. 

Sorry if this made no sense, but I was able to do this pretty easily in Excel.... now I am unsure of how to do it in Power Bi. Any help, please? Thank y'all 🙂 

Here is a link with the tier structure for different scenarios and then a box of sample analysis with my main equation. Thank you so much: https://1drv.ms/x/s!AtT91FiUJMdTgmY6risd9ngx33wj?e=Ggkpxg

[v-rongtiep-msft · Accepted solution]

Hi @Anonymous ,

For special reasons I can't view your files and upload the sample file I created, which I uploaded to you in the form of screenshots. Please have a try.

My raw data:

According to you conditions, I created two columns.

scenary =
IF (
    'Table'[daily consumption] <= 15000
        && 'Table'[demand] <= 50,
    "scenario 1",
    IF (
        'Table'[daily consumption] > 15000
            && 'Table'[demand] < 50,
        "scenario 2",
        "scenario 3"
    )
)

Column =
VAR _scenario1 =
    IF (
        'Table'[scenary] = "scenario 1",
        'Table'[daily consumption] * 0.5,
        BLANK ()
    )
VAR _scenery2 =
    IF (
        'Table'[scenary] = "scenario 2",
        15000 * 0.5 + ( 'Table'[daily consumption] - 15000 ) * 0.4,
        BLANK ()
    )
VAR _secery3 =
    IF (
        'Table'[scenary] = "scenario 3",
        15000 * 0.5 + ( 'Table'[daily consumption] - 15000 ) * 0.4,
        BLANK ()
    )
RETURN
    IF (
        'Table'[scenary] = "scenario 1",
        _scenario1,
        IF (
            'Table'[scenary] = "scenario 2",
            _scenery2,
            IF ( 'Table'[scenary] = "scenario 3", _secery3, BLANK () )
        )
    )

If I have misunderstood your meaning, please provide screenshoots with some sample data and your desired output.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.