CAGR for Group

Question

HI guys,&nbsp;&nbsp;Please could I get help with the following:&nbsp;I ned to calculate a CAGR value for a group, I have done so with the individual business units on a link and stacked bar chart but I need a grop total as well&nbsp;&nbsp;Sample Table&nbsp;Table 1&nbsp;&nbsp;Pillar&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Business Unit&nbsp; &nbsp; &nbsp; &nbsp; Income Type&nbsp; &nbsp; Statement Type&nbsp; &nbsp; &nbsp;yr1&nbsp; &nbsp; &nbsp; yr2&nbsp; &nbsp; &nbsp; value 1&nbsp; &nbsp; &nbsp; value 2&nbsp; &nbsp; &nbsp;CAGR&nbsp; &nbsp; &nbsp; &nbsp;Growth type&nbsp;xxx&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; xx&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Revenue&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Actual&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2017&nbsp; &nbsp; &nbsp; 2018&nbsp; &nbsp; &nbsp; &nbsp; 444&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;874&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 76%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; inorganic&nbsp;yyy&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; yy&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Revenue&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Budget&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2018&nbsp; &nbsp; &nbsp; &nbsp;2017&nbsp; &nbsp; &nbsp; &nbsp;7777&nbsp; &nbsp; &nbsp; &nbsp; 678&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 23%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;organic&nbsp;zzz&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; zz&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Revenue&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Actual&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2019&nbsp; &nbsp; &nbsp; &nbsp;2021&nbsp; &nbsp; &nbsp; &nbsp;999&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 8653&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;87%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; inorganic&nbsp;aaa&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;aa&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Revenue&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Board Pack&nbsp; &nbsp; &nbsp; &nbsp; 2020&nbsp; &nbsp; &nbsp; &nbsp;2017&nbsp; &nbsp; &nbsp; &nbsp;8776&nbsp; &nbsp; &nbsp; &nbsp; 367&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 93%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;inorganic&nbsp;bbb&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; bb&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Revenue&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Actual&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2021&nbsp; &nbsp; &nbsp; &nbsp;2023&nbsp; &nbsp; &nbsp; &nbsp;867&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 355&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;23%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; inorganic&nbsp;ccc&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; cc&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Revenue&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Actual&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2022&nbsp; &nbsp; &nbsp; &nbsp;2022&nbsp; &nbsp; &nbsp; &nbsp; 768&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 324&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 12%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;inorganic&nbsp;ddd&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; dd&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Revenue&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Actual&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2023&nbsp; &nbsp; &nbsp; &nbsp;2020&nbsp; &nbsp; &nbsp; &nbsp; 7654&nbsp; &nbsp; &nbsp; &nbsp; 352&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;78%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;organic&nbsp;bbb&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; bb&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Revenue&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Actual&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2024&nbsp; &nbsp; &nbsp; 2024&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7655&nbsp; &nbsp; &nbsp; &nbsp; 876&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;56%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; organic&nbsp;ccc&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; cc&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Revenue&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Actual&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2025&nbsp; &nbsp; &nbsp; &nbsp;2025&nbsp; &nbsp; &nbsp; &nbsp; 8778&nbsp; &nbsp; &nbsp; &nbsp;987&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 43%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;organic&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;with inorganic&nbsp;ddd&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; dd&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Revenue&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Actual&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2025&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;inorganic&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;with organic&nbsp;&nbsp;Outcome&nbsp;&nbsp;23%&nbsp; &nbsp; &nbsp; &nbsp; 15%&nbsp; &nbsp; &nbsp; &nbsp;19%&nbsp; &nbsp; &nbsp; &nbsp;20%&nbsp; &nbsp; &nbsp; &nbsp; 12%&nbsp; &nbsp; &nbsp; 19%&nbsp; &nbsp; &nbsp; ????xx&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;yy&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;zz&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; dd&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;cc&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;bb&nbsp; &nbsp; &nbsp; Group&nbsp;&nbsp;&nbsp;&nbsp;Thanks in advance&nbsp;Zaynah&nbsp;

alexisolson · Answer

Can you explain how you're computing the values you show under Outcome?

zaynah16 · Answer

This is the power bi visual. The CAGR values are calculated in Acess SQL because this current table is a query table table from several other tables .

This is the SQL in acess

SELECT t1.pillar, t1.[Business Unit], t1.[Income type], t1.[Statement type], t1.Year AS yr_1, t2.Year AS yr_2, t1.value AS value_1, t2.value AS value_2, ((t1.value/t2.value)^(1/((IIf(t1.Year='2025 with Inorganic','2025',t1.Year))-(IIf(t2.Year='2025 with Inorganic','2025',t2.Year))))-1) AS cagr, IIf([t2].[Year]="2025 with Inorganic","Inorganic and Organic",
IIf([t2].[Year]="Net Organic Growth","Net Organic Growth","Organic")) AS [Growth Type]
FROM 20172025revenues AS t1 INNER JOIN 20172025revenues AS t2 ON (t1.pillar = t2.pillar) AND (t1.[Business Unit] = t2.[Business Unit]) AND (t1.[Income type] = t2.[Income type])
WHERE (((t1.[Business Unit])<>'Other And Consolidation Entries'));

alexisolson · Answer

If you're calculating CAGR in Access, then that's where you should calculate the group CAGR too since it's not an additive measure you can just aggregate in Power BI. If you go this route, you may be interested in reading this post I wrote a while back:Handling Subtotals for Pre-Calculated (Non-Additive) Measures
&nbsp;
The other option is to do all your CAGR calculations in Power BI rather than Access. DAX can handle these sorts of calculations just fine and you don't have to worry about the granularity scope as much.

zaynah16 · Answer

Do you have a solution on how to calculate the CAGRS in power bi ?&nbsp;

alexisolson · Answer

It's a simple formula in principle but I'm not sure how you aggregate separate timeframes together.
&nbsp;
If you can provide some sample input and expected output, I can probably help. Your given sample data doesn't make much sense to me though. The value 1 and value 2 don't appear to correspond to the CAGR in that row.

Forum Discussion

CAGR for Group

5 Replies

Related content

CAGR computation

Fabric & Azure Data User Group Program Requirements & Benefits

Community User Group Experience

CAGR

User Group Leader Get Started Guide