Using SUM to the Power over a Period of Time

[Low_Power_BI]

Hi,

I have recently started using Power BI after many years of excel.

I have a model where I need to know the future (escalated) costs (X) in a specific year and also the total (escalated) costs over a period of time (Y) based on an escalation rate (Z)

So far this has been relatively straight forward and I have plotted a column chart that does this well, showing the base cost in each year and the escalation of the cost in that specific year. i.e. Cost in Year 5 = Base Cost (X) * ( Escalation Rate (Z) ^ 5)

I've done this with the following formula in Power BI:

Escalated Cost = Base Cost * ( 1 + Escalation Rate ) ^ ( SUM ( Year Data Input) )

The Base Cost and the Escalation Rate are What If parameters and the Year Data Input can be the Years 1 - 20 for this example.

The graph works wonderfully with the Base Cost and Escalated Cost plotted against the Year dataset.

However, I would like to know the total Escalated Cost over 20 Years, the SUM of all the Escalated Costs. The SUM of the Base Costs works easily and I have that displayed on my dashboard with a card, however whilst the in specific Year the escalated cost is correct, when I display the SUM of the escalated Costs, it does so to the Power of 1 + 2 + 3 + .... + 19 + 20, when I really want is the SUM of the Escalated Values displayed in the Column Chart.

This is not a simple to the power of 20 calculation as not every year has a cost associated with it, otherwise I would use the MAX function.

Any ideas or suggestions? Appreciate any help on offer, many thanks in advance!

[Greg_Deckler]

Could you post some sample/mock data?

[Low_Power_BI]

Sure,

A bit of context, this is planning the cost of maintenance for mechnical components which occurs every several years depending on the time between maintenance and the hours the component is used for each year.

Cost in that Year = ROUNDDOWN ( SUM [Year] / ( Time Between Maintenance / Hours Used per Year ) , 0 )

This returns a whole number and represents either the 1st, 2nd or 3rd etc maintenance event which is then multiplied by the Cost of Maintenance.

There's a distinct lack of data inputs as this is a modelling project based on forward looking assumptions rather than data analysis of the past. The only data table input I have is the Years (below) the rest are What If variable parameters (Cost, Time Between Maintenance, Hours per Year etc)

Year

0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

[v-chuncz-msft]

@Low_Power_BI,

The above is still not very clear. You may try to use SUMX Function.

Measure2 =
SUMX ( Table1, [Measure1] )

[Low_Power_BI]

Hi All,

Apologies for the lack of clarity.

Table 1 is above for the Year in Data Inputs, below is the Measure Escalation Factor and the What If Parameter Escalation.

Escalation Factor = ( 1 + Escalation[Escalation Value] ) ^ (SUM('Data Inputs'[Year]))

For the Maintenance Cost the following Measure is used with Time Between Maintenance, Hours per Year and Maintenance Price all being What If parameters.

Maintenance Base Cost = ROUNDDOWN ( SUM('Data Inputs'[Year]) / ( 'Time Between Maintenance'[Time Between Maintenance Value] / Hours per Year[Hours per Year Value] ) ) , 0 ) * 'Maintenance Price'[Maintenance Price Value]

These two measures are then plotted together in a stacked column chart with Year on the horizontal axis to give the below graph in the correct format:

Base Cost with Cost of Escalation on Top

The SUM of the Base Maintenance Costs can easily be displayed on the Dashboard as Card, but the SUM of these Escalation values returns ^ 20 rather than the SUM of these costs in the individual years...

Hope that's clear and appreciate the comments

[Low_Power_BI]

@v-chuncz-msft@Greg_Deckler

Also, here is some dummy data and the confusing math behind the graph...

Clearly the first column sums the desired result along with the second displaying the correct figures year on year, however the second SUM is not the values in the table but the SUM of all the Years to the Power creating a massive figure.

Appreciate any tips to resolve this issue, this is an import of an excel model that works through linking calculated columns in excel that aren't possible in Power BI with What If parameters.

[v-chuncz-msft]

@Low_Power_BI,

Note that when creating a What if parameter, you are using a measure not calculated column.