Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
Try this for your 2014Rev measure:
2014Rev = sumX(FILTER(ALL(FullyMappedData),FullyMappedData[Year]=2014),FullyMappedData[Revenue (USD)])
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
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 |
Try this for your 2014Rev measure:
2014Rev = sumX(FILTER(ALL(FullyMappedData),FullyMappedData[Year]=2014),FullyMappedData[Revenue (USD)])
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.
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |