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, produc...
Need Help
  • 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)])
Greg_Deckler's avatar
Greg_Deckler
Community Champion
8 years ago

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
8 years ago

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

 

 

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    Perfect.

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    Try this for your 2014Rev measure:

     

    2014Rev = sumX(FILTER(ALL(FullyMappedData),FullyMappedData[Year]=2014),FullyMappedData[Revenue (USD)])
    • hholtshopple's avatar
      hholtshopple
      Frequent Visitor
      8 years ago

      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!