Add the CAGR as a card visual to the page. This calculation uses the prior three measures we created. Add one final measure and add the following equation to calculate CAGR: CAGR = (/)^(1/)-1 Ending Value Measure as Card VisualĬombining all the previous measures we will now calculate the CAGR value. I have highlighted the Ending Value measure as a card for an example. A measure is illustrated by the little calculator image next to the measure. Next add a Card visual for each new measure we added. Your fields list should now look like the following: Fields List with Measures This calculation will change based on the selections in the page view.Īdd two more measures for Ending Value and # of years Ending Value = CALCULATE(SUM('World GDP'),FILTER('World GDP','World GDP'=MAX('World GDP'))) # of Years = (MAX('World GDP')-MIN('World GDP')) This equation totals all the items in the table called World GDP in the column labeled GDP. Enter the following equation for the beginning value: Beginning Value = CALCULATE(SUM('World GDP'),FILTER('World GDP','World GDP'=MIN('World GDP'))) On the Home ribbon click the button labeled New Measure. For reference the CAGR calculation is as follows: (found from ) CAGR Calculationįor each variable on the right of the equation we will create one measure one for Ending Value, Beginning Value and # of Years. Next we will build a number of measure that will calculate the required variables to be used in our CAGR calculation. Loading the query loads the following columns into the fields bar on the right hand side of the screen. Rename the Query to World GDP and then on the home ribbon click Close & Apply. Click Done to load the query into the the Query Editor. Paste the code above into the advance editor.
#CAGR FORMULA EXCEL HOW TO#
Note: The tutorial on how to copy and paste the code into the Query Editor is located here. Source = Excel.Workbook(Web.Contents(""), null, true),ĮconomicData_Table = Source) Below is the Query Editor code you can copy and paste directly into the Advance Editor. To make this process less about acquiring data and more about calculating the CAGR. For this tutorial we will gather data from World Bank found here.
#CAGR FORMULA EXCEL UPDATE#
By dynamic we mean as you select different items on a bar chart for example the CAGR calculation will update to reveal the CAGR calculation only for the selected data. This tutorial walks through calculating a dynamic Compound Annual Growth Rate (CAGR).