Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
117 | |
101 | |
71 | |
61 |