CAGR for Group

[Zaynah16]

HI guys, 

Please could I get help with the following: 

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 

Sample Table 

Table 1 

Pillar             Business Unit        Income Type    Statement Type     yr1      yr2      value 1      value 2     CAGR       Growth type 

xxx                  xx                        Revenue             Actual               2017      2018        444         874          76%            inorganic 

yyy                  yy                        Revenue             Budget             2018       2017       7777        678          23%             organic 

zzz                  zz                         Revenue            Actual               2019       2021       999          8653         87%            inorganic 

aaa                 aa                         Revenue            Board Pack        2020       2017       8776        367          93%             inorganic 

bbb                bb                         Revenue           Actual                2021       2023       867          355           23%            inorganic 

ccc                  cc                         Revenue            Actual               2022       2022        768          324          12%             inorganic 

ddd                dd                         Revenue           Actual                2023       2020        7654        352           78%           organic 

bbb                bb                         Revenue           Actual                2024      2024         7655        876           56%            organic 

ccc                  cc                         Revenue            Actual               2025       2025        8778       987            43%               organic                                                                                                                                 with inorganic 

ddd                dd                         Revenue           Actual                2025                                                                         inorganic 

                                                                                                     with organic 

Outcome 

23%        15%       19%       20%        12%      19%      ????

xx             yy         zz          dd           cc           bb      Group 

Thanks in advance 

Zaynah 

[AlexisOlson]

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

[Zaynah16]

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]

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

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]

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

[AlexisOlson]

It's a simple formula in principle but I'm not sure how you aggregate separate timeframes together.

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.