topic Growth Rate Measure that SUMS Growth Rate Column instead of calculating Total Row in DAX Commands and Tips

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

mothert — Tue, 09 Nov 2021 14:29:22 GMT

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,

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

amitchandak — Tue, 09 Nov 2021 15:03:05 GMT

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

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

Jihwan_Kim — Tue, 09 Nov 2021 15:09:24 GMT

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] ) )

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

mothert — Tue, 09 Nov 2021 15:14:40 GMT

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.

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

mothert — Tue, 09 Nov 2021 15:19:02 GMT

Jihwan_Kim That worked! Thank you!