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.
Hi, this is a sample of my data source:
Geo-Location | Type | Rent | Month | Year |
London | Studio | $1,653 | Jan-10 | 2010 |
London | Studio | $1,646 | Feb-10 | 2010 |
London | Studio | $1,575 | Mar-10 | 2010 |
London | 1 BDR | $1,677 | Jan-10 | 2010 |
London | 1 BDR | $1,971 | Feb-10 | 2010 |
London | 1 BDR | $1,920 | Mar-10 | 2010 |
NYC | Studio | $1,050 | Jan-10 | 2010 |
NYC | Studio | $1,075 | Feb-10 | 2010 |
NYC | Studio | $1,150 | Mar-10 | 2010 |
NYC | 1 BDR | $1,123 | Jan-10 | 2010 |
NYC | 1 BDR | $1,113 | Feb-10 | 2010 |
NYC | 1 BDR | $1,246 | Mar-10 | 2010 |
I want to get an annual avarege change (%) but for some reason the result isn't quit accuarate:
%StudioRent = CALCULATE(IF(HASONEVALUE('Rental Trends'[Year]), DIVIDE(SUM('Rental Trends'[Rent]), CALCULATE(SUM('Rental Trends'[Rent]), 'Rental Trends'[Year] = FORMAT(VALUES('Rental Trends'[Year]) - 1, BLANK())))) - 1,'Rental Trends'[Type] = "Studio")
Does someone know how to achieve this?
Thanks
Solved! Go to Solution.
Hi @v-shex-msft,
Based on your answer I re-write formula and got what I needed (this refers to the previous year; the formula for the current year remains the same):
AVG Rent Studio PY = IF ( HASONEVALUE ( 'Rental Trend'[Year] ), CALCULATE ( AVERAGE ( 'Rental Trend'[Rent] ), FILTER ( ALL( 'Rental Trend' ), 'Rental Trend'[Year] = MAX( 'Rental Trend'[Year] ) - 1 ), VALUES ( 'Rental Trend'[Type] ) ) )
This piece of formula filtered a previous year:
PY = MAX( 'Rental Trend'[Year] ) - 1
Year to year change:
AnnualChange = 1 - DIVIDE([AVG Rent PY], [AVG Rent CY])
Thank you.
After some research around I got an idea and did this finaly.
Measure 1 (for previous year):
AVG Rent Studio PY = CALCULATE(IF(HASONEVALUE('Rental Trend'[Year]), CALCULATE(AVERAGE('Rental Trend'[Rent]), 'Rental Trend'[Year] = FORMAT(VALUES('Rental Trend'[Year]) - 1, BLANK()), 'Rental Trend'[Type] = "Studio")))
Measure 2 (selected year):
AVG Rent Studio CY = IF(HASONEVALUE('Rental Trend'[Year]), CALCULATE(AVERAGE('Rental Trend'[Rent]), 'Rental Trend'[Type] = "Studio"))
Measure 3 (year to year change):
Y/Y Rent Change = 1 - DIVIDE([AVG Rent Studio PY],[AVG Rent Studio CY])
And the result looks like this:
Can I rewrite this solution and make it dynamic (showing year-to-year change based on "Type" selection (Studio, 1BDR, 2BDR)?
Thanks,
Hi @LakiCG,
You can try to use below formula:
AVG Rent Studio PY = IF ( HASONEVALUE ( 'Rental Trend'[Year] ), CALCULATE ( AVERAGE ( 'Rental Trend'[Rent] ), FILTER ( ALLSELECTED ( 'Rental Trend' ), 'Rental Trend'[Year] = SELECTEDVALUE ( 'Rental Trend'[Year] ) - 1 ), VALUES ( 'Rental Trend'[Type] ) ) ) AVG Rent Studio CY = IF ( HASONEVALUE ( 'Rental Trend'[Year] ), CALCULATE ( AVERAGE ( 'Rental Trend'[Rent] ), VALUES ( 'Rental Trend'[Type] ) ) )
Regards,
Xiaoxin Sheng
Hi @v-shex-msft,
Based on your answer I re-write formula and got what I needed (this refers to the previous year; the formula for the current year remains the same):
AVG Rent Studio PY = IF ( HASONEVALUE ( 'Rental Trend'[Year] ), CALCULATE ( AVERAGE ( 'Rental Trend'[Rent] ), FILTER ( ALL( 'Rental Trend' ), 'Rental Trend'[Year] = MAX( 'Rental Trend'[Year] ) - 1 ), VALUES ( 'Rental Trend'[Type] ) ) )
This piece of formula filtered a previous year:
PY = MAX( 'Rental Trend'[Year] ) - 1
Year to year change:
AnnualChange = 1 - DIVIDE([AVG Rent PY], [AVG Rent CY])
Thank you.
Let me ask this in another way. Using the above table I calculated an average rent using this formula (for "Studio" type):
AVGRentStudio = CALCULATE(AVERAGE('Rental Trend'[Rent]),'Rental Trend'[Type] = "Studio")
And I presented the average values per year in tabular form:
Now, I would like to show the percentage change (compared to the previous year). Can anyone help to achieve this?
Thanks,
Hi @LakiCG,
You can try to use below measure if it works for your scenario:
Change % with Prev Year = VAR _Curr = CALCULATE ( AVERAGE ( Table1[Rent] ), VALUES ( Table1[Geo-Location] ), VALUES ( Table1[Year] ) ) VAR _prev = CALCULATE ( AVERAGE ( Table1[Rent] ), FILTER ( ALLSELECTED ( Table1 ), Table1[Year] = SELECTEDVALUE ( Table1[Year] ) - 1 ), VALUES ( Table1[Geo-Location] ) ) RETURN DIVIDE ( _Curr - _prev, _prev, -1 )
Regards,
Xiaoxin Sheng
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 |
---|---|
112 | |
97 | |
83 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |