cancel
Showing results for
Did you mean:
Frequent Visitor

## Indexed growth Rate

Hello!

This is my first post, just learning how to use PowerBi as I go!

I am trying to index a growth rate to the first year in my data set(2014) . Data set is revenue by year, region, product line. I would like to be able to filter on region and product line so I dont want to use a static denominator.

I have Year as a text and I have also converted it to a Date in two seperate columns in order to make YoY growth rate work.

For year on year growth I used the below:

CurrentRev = sum(FullyMappedData[Revenue (USD)])

PriorYearRev = CALCULATE([CurrentRev], PREVIOUSYEAR(FullyMappedData[year2]))

RevGrowth = DIVIDE([CurrentRev], [PriorYearRev],1 ) - 1

For Indexed Growth I tried to use ([Year] is text here, [year2] is a date)

2014Rev = calculate(sum(FullyMappedData[Revenue (USD)]), FullyMappedData[Year]=2014)

CurrentRev = sum(FullyMappedData[Revenue (USD)])

indexRevGrowth = DIVIDE([CurrentRev], [2014Rev], 1)-1

I am not sure why 2014 revnue does not fill down for all the years, but this seems to be why my index growth rate is 0 throughout?

Any help or suggestions would be appreciated! I am sure there is even some better code to use.

1 ACCEPTED SOLUTION
Super User

Try this for your 2014Rev measure:

`2014Rev = sumX(FILTER(ALL(FullyMappedData),FullyMappedData[Year]=2014),FullyMappedData[Revenue (USD)])`

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Learn Power BI 2nd Edition
5 REPLIES 5
Super User

Your 2014Rev does not fill down because in the context of the other rows for 2015,2016,etc, there are no rows that match 2014 for the year. So blank. Measures are context sensitive. So, you would need to use an ALL statement somewhere in your calculation or an ALLEXCEPT and exclude the year from that so that you change the context in which the measure is being calculated.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Learn Power BI 2nd Edition
Frequent Visitor

I have attached a sample data set below, is this the best way to do that?

 Year Corporate Account Sub Geomarket (Code) Service Segment Category Revenue (USD) Spend  (USD) year2 2014 Apple USCL G&G and Reservoir Services 1100 0 1/1/2014 2014 Apple USCL Technical Software 1231 0 1/1/2014 2014 Apple USCL Seismic Data Processing 165465 0 1/1/2014 2014 Apple USCL Seismic Data Processing 4654 0 1/1/2014 2014 Apple USCL Technical Software 4643 0 1/1/2014 2014 Apple USCL Seismic Data Processing 4645 0 1/1/2014 2014 Apple USCL Wireline and Geoscience Services 46453 0 1/1/2014 2014 Apple USRK Drilling Fluids 464 0 1/1/2014 2014 Apple USRK Wireline and Geoscience Services 13006.33 0 1/1/2014 2014 Apple USRK Technical Software 10915.83 0 1/1/2014 2014 Apple USRK Other 8825.333 0 1/1/2014 2014 Apple USRK Technical Software 6734.833 0 1/1/2014 2014 Apple USRK Other 4644.333 0 1/1/2014 2014 Apple USSE Artificial Lift Services 2553.833 0 1/1/2014 2015 Apple USCL Drilling Fluids 463.3333 0 1/1/2015 2015 Apple USCL G&G and Reservoir Services 416546 0 1/1/2015 2015 Apple USCL Specialty Chemicals 464 0 1/1/2015 2015 Apple USCL Technical Software 46854 0 1/1/2015 2015 Apple USCL Well Stimulation Services 874 0 1/1/2015 2015 Apple USCL Cementing 484 0 1/1/2015 2015 Apple USRK Wireline and Geoscience Services 11 0 1/1/2015 2015 Apple USRK Artificial Lift Services 4464 0 1/1/2015 2015 Apple USRK Wireline and Geoscience Services 141 0 1/1/2015 2016 Apple NAL_HQ Artificial Lift Services 1100 0 1/1/2016 2016 Apple NAL_HQ Wireline and Geoscience Services 1231 0 1/1/2016 2016 Apple NAL_HQ Drilling Fluids 165465 0 1/1/2016 2016 Apple USCL Drilling Services 4654 0 1/1/2016 2016 Apple USCL Drilling Tools 4643 0 1/1/2016 2016 Apple USCL Drilling Fluids 4645 0 1/1/2016 2016 Apple USCL Drilling Services 46453 0 1/1/2016 2016 Apple USCL Drilling Tools 464 0 1/1/2016 2016 Apple USLE Specialty Chemicals 13006.33 0 1/1/2016 2016 Apple USRK Technical Software 10915.83 0 1/1/2016 2016 Apple USRK Well Stimulation Services 8825.333 0 1/1/2016 2017 Apple NAL_HQ Cementing 6734.833 0 1/1/2017 2017 Apple NAL_HQ Wireline and Geoscience Services 4644.333 0 1/1/2017 2017 Apple USCL Artificial Lift Services 2553.833 0 1/1/2017 2017 Apple USCL Drilling Fluids 463.3333 0 1/1/2017 2017 Apple USCL Drilling Services 416546 0 1/1/2017 2017 Apple USCL Drilling Tools 464 0 1/1/2017 2017 Apple USCL Specialty Chemicals 46854 0 1/1/2017

Super User

Try this for your 2014Rev measure:

`2014Rev = sumX(FILTER(ALL(FullyMappedData),FullyMappedData[Year]=2014),FullyMappedData[Revenue (USD)])`

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Learn Power BI 2nd Edition
Frequent Visitor

Greg,

I was able to see my error. I ended up pulling the year2 out of the columun and could easily display Indexed Growth rate.

Super User

Perfect.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Learn Power BI 2nd Edition

Announcements

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

#### What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.