Forum Discussion

hholtshopple's avatar
hholtshopple
Frequent Visitor
8 years ago
Solved

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!

  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago

    Try this for your 2014Rev measure:

     

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

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion

    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

    • hholtshopple's avatar
      hholtshopple
      Frequent Visitor

      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