Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have several buildings where i want to get the vacancy rate for the last five years. I can't seem to figure out how to get all one building on a single row. Ideally it would look like this at the end of it:
2011 | 2012 | 2013 | 2014 | 2015 | 2016 | |||||||||||||||||||
PropertyID | Property Name | 1 | 2 | 3 | 4 | 1 | 2 | 3 | 4 | 1 | 2 | 3 | 4 | 1 | 2 | 3 | 4 | 1 | 2 | 3 | 4 | 1 | 2 | 3 |
39876 | Bemiston Tower | 11.08% | 11.08% | 9.67% | 9.67% | 9.12% | 9.12% | 10.72% | 9.38% | 10.33% | 9.52% | 18.98% | 18.98% | 11.75% | 11.75% | 11.74% | 13.35% | 12.34% | 12.34% | 12.41% | 12.41% | 10.46% | 9.40% | 8.87% |
42132 | West Park I | 16.79% | 15.68% | 15.68% | 15.68% | 9.71% | 9.71% | 0.00% | 9.71% | 1.62% | 0.00% | 0.00% | 0.00% | 1.74% | 1.74% | 0.00% | 1.08% | 1.08% | 7.05% | 7.05% | 7.05% | 7.05% | 1.08% | 1.08% |
42716 | 77 Building | 18.05% | 18.05% | 18.05% | 18.05% | 18.05% | 7.27% | 7.42% | 7.67% | 7.67% | 7.67% | 12.08% | 12.08% | 15.16% | 15.16% | 15.16% | 15.16% | 15.80% | 15.80% | 27.68% | 27.68% | 27.68% | 44.87% | 44.87% |
But when i try it looks like this:
PropertyID | StatDate (Year.Quarter) |
39876 | 2010.4 |
39876 | 2011.4 |
39876 | 2012.4 |
39876 | 2013.4 |
39876 | 2014.4 |
39876 | 2015.4 |
39876 | 2016.4 |
42132 | 2010.4 |
42132 | 2011.4 |
42132 | 2012.4 |
42132 | 2013.4 |
42132 | 2014.4 |
42132 | 2015.4 |
42132 | 2016.4 |
42716 | 2010.4 |
42716 | 2011.4 |
42716 | 2012.4 |
42716 | 2013.4 |
42716 | 2014.4 |
42716 | 2015.4 |
42716 | 2016.4 |
I have a date column StatDate which has year, quarter, day, and month if needed. I tried creating a concatenate with that field "StatDate (Year.Quarter) = CONCATENATE('Building Data'[StatDate].[Year],CONCATENATE(".",'Building Data'[StatDate].[QuarterNo]))" in order to do a lookup - "VacancyRate2012 = LOOKUPVALUE('Building Data'[VacancyRate Stat Column],'Building Data'[StatDate (Year.Quarter)],"2012.4")" to return each quarter that i wanted, but the error i receive is "A table of multiple values was supplied where a single value was expected.
Any Idea how i can do this?
What you've pasted above is a Pivot Table with 2 Row and 2 Column Items.
Can you instead paste a sample of the source tables for that Pivot Table?
PropertyID Vacancy Rate StatDate
39876 0.11 1/1/2014
39876 0.09 1/1/2015
39876 0.08 1/1/2016
39876 0.07 4/1/2016
39876 0.06 7/1/2016
39876 0.08 9/1/2016
39876 0.12 4/1/2015
The database is a SQL server database. The first table i copied in there from how i was able to create it in Tableau but i'd like to do that in PowerBI. Is this what you were asking for?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
103 | |
76 | |
66 | |
63 |
User | Count |
---|---|
142 | |
105 | |
102 | |
81 | |
68 |