Forum Discussion
Complex Tier Calculations with Nested If Statements
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
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.
1 Reply
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.