cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hholtshopple
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. 

 

Thank you in advanced!

1 ACCEPTED SOLUTION

Try this for your 2014Rev measure:

 

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

@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
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.

 

Also, sample data would assist greatly. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

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

 

YearCorporate AccountSub Geomarket (Code)Service Segment CategoryRevenue (USD)Spend  (USD)year2
2014AppleUSCLG&G and Reservoir Services110001/1/2014
2014AppleUSCLTechnical Software123101/1/2014
2014AppleUSCLSeismic Data Processing16546501/1/2014
2014AppleUSCLSeismic Data Processing465401/1/2014
2014AppleUSCLTechnical Software464301/1/2014
2014AppleUSCLSeismic Data Processing464501/1/2014
2014AppleUSCLWireline and Geoscience Services4645301/1/2014
2014AppleUSRKDrilling Fluids46401/1/2014
2014AppleUSRKWireline and Geoscience Services13006.3301/1/2014
2014AppleUSRKTechnical Software10915.8301/1/2014
2014AppleUSRKOther8825.33301/1/2014
2014AppleUSRKTechnical Software6734.83301/1/2014
2014AppleUSRKOther4644.33301/1/2014
2014AppleUSSEArtificial Lift Services2553.83301/1/2014
2015AppleUSCLDrilling Fluids463.333301/1/2015
2015AppleUSCLG&G and Reservoir Services41654601/1/2015
2015AppleUSCLSpecialty Chemicals46401/1/2015
2015AppleUSCLTechnical Software4685401/1/2015
2015AppleUSCLWell Stimulation Services87401/1/2015
2015AppleUSCLCementing48401/1/2015
2015AppleUSRKWireline and Geoscience Services1101/1/2015
2015AppleUSRKArtificial Lift Services446401/1/2015
2015AppleUSRKWireline and Geoscience Services14101/1/2015
2016AppleNAL_HQArtificial Lift Services110001/1/2016
2016AppleNAL_HQWireline and Geoscience Services123101/1/2016
2016AppleNAL_HQDrilling Fluids16546501/1/2016
2016AppleUSCLDrilling Services465401/1/2016
2016AppleUSCLDrilling Tools464301/1/2016
2016AppleUSCLDrilling Fluids464501/1/2016
2016AppleUSCLDrilling Services4645301/1/2016
2016AppleUSCLDrilling Tools46401/1/2016
2016AppleUSLESpecialty Chemicals13006.3301/1/2016
2016AppleUSRKTechnical Software10915.8301/1/2016
2016AppleUSRKWell Stimulation Services8825.33301/1/2016
2017AppleNAL_HQCementing6734.83301/1/2017
2017AppleNAL_HQWireline and Geoscience Services4644.33301/1/2017
2017AppleUSCLArtificial Lift Services2553.83301/1/2017
2017AppleUSCLDrilling Fluids463.333301/1/2017
2017AppleUSCLDrilling Services41654601/1/2017
2017AppleUSCLDrilling Tools46401/1/2017
2017AppleUSCLSpecialty Chemicals4685401/1/2017

 

 

Try this for your 2014Rev measure:

 

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

@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

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. 

 

I truly appreciate your help!

Perfect.


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

Helpful resources

Announcements
Microsoft Build 768x460.png

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_carousel_with_text (1).png

Charticulator Design Challenge

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

May UG Leader Call Carousel 768x460.png

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.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!