CAGR and Linear Guage Visual in Power BI

[Surekha_PM]

Hello Everyone,

I am trying to create CAGR formula for last 5 years, next three years and current year. And from this calculation, I need to create a linear gauge chart where I need to put all three metrics to show the performance of the company. Now, I am able to create the formula, but it is not showing me the table based on the sub-category. I am not able to understand what the issue is.

Could you please help me out with this?

Table:

Year	SubCategory	SalesValue
2018	A	0
2018	B	7511700000
2018	C	283600000
2018	D	28597000000
2018	E	0
2018	F	0
2019	A	0
2019	B	8578400000
2019	C	306200000
2019	D	31142200000
2019	E	0
2019	F	0
2020	A	0
2020	B	8449700000
2020	C	288700000
2020	D	30270200000
2020	E	0
2020	F	0
2021	A	0
2021	B	9198300000
2021	C	307800000
2021	D	33118600000
2021	E	0
2021	F	0
2022	A	0
2022	B	10361900000
2022	C	339900000
2022	D	36986800000
2022	E	0
2022	F	0
2023	A	0
2023	B	11550400000
2023	C	374200000
2023	D	41096800000
2023	E	0
2023	F	0
2024	A	0
2024	B	12822500000
2024	C	411000000
2024	D	45564000000
2024	E	0
2024	F	0
2025	A	0
2025	B	14204800000
2025	C	449900000
2025	D	50316400000
2025	E	0
2025	F	0
2026	A	0
2026	B	15687700000
2026	C	491400000
2026	D	55408400000
2026	E	0
2026	F	0
2027	A	0
2027	B	17298900000
2027	C	529100000
2027	D	60827300000
2027	E	0
2027	F	0
2028	A	0
2028	B	19031300000
2028	C	569300000
2028	D	66636300000
2028	E	0
2028	F	0

Formula:

CAGR % 2018-2023 =

VAR StartYear = 2018

VAR EndYear = 2023

VAR StartingValue = CALCULATE(SUM('Table'[SalesValue]), 'Table'[Year] = StartYear)

VAR EndingValue = CALCULATE(SUM('Table'[SalesValue]), 'Table'[Year] = EndYear)

VAR NoOfYears = EndYear - StartYear

RETURN

IF (

    NOT ISBLANK(StartingValue) && NOT ISBLANK(EndingValue),

    (( (EndingValue / StartingValue) ^ (1 / NoOfYears) ) - 1),

    BLANK()

)

Regards,
Surekha

[bhanu_gautam · Accepted solution]

@Surekha_PM , 

CAGR for 2018-2023: Create a new measure

CAGR_2018_2023 =
VAR StartYear = 2018
VAR EndYear = 2023
VAR NoOfYears = EndYear - StartYear

CAGR for current year

CAGR_Current_Year =
VAR StartYear = 2023
VAR EndYear = 2024
VAR NoOfYears = EndYear - StartYear
RETURN
CALCULATE(
(( (SUMX(FILTER('Table', 'Table'[Year] = EndYear), 'Table'[SalesValue]) /
SUMX(FILTER('Table', 'Table'[Year] = StartYear), 'Table'[SalesValue]) ) ^ (1 / NoOfYears) ) - 1),
ALLEXCEPT('Table', 'Table'[SubCategory])
)
RETURN
CALCULATE(
(( (SUMX(FILTER('Table', 'Table'[Year] = EndYear), 'Table'[SalesValue]) /
SUMX(FILTER('Table', 'Table'[Year] = StartYear), 'Table'[SalesValue]) ) ^ (1 / NoOfYears) ) - 1),
ALLEXCEPT('Table', 'Table'[SubCategory])
)

Create similary one more for next 3 years

Create a new table that includes the SubCategory and the calculated CAGR values to use in Linear Gauge Chart

CAGR_Table =
SUMMARIZE(
'Table',
'Table'[SubCategory],
"CAGR_2018_2023", [CAGR_2018_2023],
"CAGR_2023_2028", [CAGR_2023_2028],
"CAGR_Current_Year", [CAGR_Current_Year]
)