Growth Rate Measure that SUMS Growth Rate Column instead of calculating Total Row

[mothert]

Hi,

I am new to Power BI and DAX. I am trying to calculate a simple 3-year growth rate like  this example from excel:

Age	2018	2019	2020	Growth Rate
<= 20	6	3	4	-33.33%
21-25	7	11	11	57.14%
26-30	7	5	5	-28.57%
31-40	3	2	3	0.00%
41-50	1	1	0	-100.00%
51-60	0	0	0	0.00%
61 >	0	0	0	0.00%
Total	24	22	23	-4.17%

However when I do this in Power BI and create a measure using the following:

Growth Rate = DIVIDE( [2020]-[2018], [2018], BLAK())

It sums the Growth rate column Total row, instead of calculating the growth rate for the Total row. See image:

I don't want -104.76% I want it to calculate the growth rate for the 'Total' row.

Any ideas?

Thanks,

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a measure.

Growth Rate: =

DIVIDE ( SUM ( Data[2020] ) - SUM ( Data[2018] ), SUM ( Data[2018] ) )

[mothert]

@Jihwan_Kim  That worked! Thank you!

[amitchandak]

@mothert , Ideally power bi should give you the same answer as excel(what you shown) . I doubt you have something on top of this measure. By default grand totals are recalculated in power bi

[mothert]

The other chart (the color one) is what is happening in Power BI. The measure I created used the formula I mentioned above:

Growth Rate = DIVIDE( [2020] - [2018], [2018]. Blank())
It gives me what you see in the color chart.