Help with static average

[Anonymous]

Hello,

I need to sum the column Amount for the past 10 years using the offset 0 to -9 then divide by 10 (because I'm using last 10 years)

Then I need a new DAX measure that will always display the result of 394.3 (total sum /10) in the Line graph no matter if I change the filter slicer date.

Year offset and Year columns come from Dim_date 

Amount comes from Fact_table

Relationship by date

YEAR OFFSET	YEAR	Amount	Result wanted
-11	2010	254	394.3
-10	2011	500	394.3
-9	2012	350	394.3
-8	2013	152	394.3
-7	2014	365	394.3
-6	2015	214	394.3
-5	2016	125	394.3
-4	2017	548	394.3
-3	2018	952	394.3
-2	2019	214	394.3
-1	2020	365	394.3
0	2021	658	394.3
		3943 (total past 10 months)
		394.3 (total / 10)

Any ideas? Thanks!!

[Jihwan_Kim]

Hi, @Anonymous 

Please check the below picture and the sample pbix file's link down below.

Amount avg past 10 years =

VAR lastyearoffset =

CALCULATE ( MAX ( Dates[YEAR OFFSET] ), ALL ( Dates ) )

VAR lasttenyearstotal =

CALCULATE (

SUM ( 'Fact'[Amount] ),

FILTER ( ALL ( Dates ), Dates[YEAR OFFSET] >= lastyearoffset - 9 )

)

RETURN

DIVIDE ( lasttenyearstotal, 10 )

https://www.dropbox.com/s/2xlekzdixcvxo13/fabnishiv4.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

Hi Jihwan,

Please see below the real data set with your formula.

For some reason the the numbers aren't matching up. So I'm returning only the result of the sum to check and the sum is way higher than it should be. The relationship between date and fact is made on quarter level repeating the day on the date table side (e.g q1 01/01/2021... in each line)

[Jihwan_Kim]

Hi, @Anonymous 

Thank you for your feedback.

I am not sure how you wrote the measure, [Area leased sqm].

Please try to put this measure into my measure, and replace it with sum() part.

Or, please share your sample pbix file's link here, then I can try to come up with a more accurate measure.

Thanks.

[Anonymous]

Hi, @Jihwan_Kim 

This is your measure,

the [Area leased sqm] is just a raw data from the fct table.

The result should be around 2,3m and not 5m

The reason is because the sum of the area leased sqm is wrong

[Jihwan_Kim]

It is quite difficult for me to step further.

please share your sample file's link.

[Anonymous]

here is the file the page, is the test page.

Dropbox

https://www.dropbox.com/s/onjewhcu077bsvt/fabtest.pbix?dl=0

Thank you

[Jihwan_Kim]

Hi, @Anonymous 

Thank you for sharing.

I am not sure how the filters are applied to the whole page, but there are some filters applied to the page level.

Please check the below picture and the measure below.

I hope the number is the one that is expected.

_Take Up - 10 Years Average2 =

VAR lastyearoffset =

CALCULATE( MAX (dim_Date[Year Offset] ) , ALL (dim_Date ) )

VAR lastyearstotal =

CALCULATE(

SUM(fct_10YR_JLLREDS_Demand[Area Leased sqm] ),

FILTER( ALLSELECTED(dim_Date ), dim_Date[Year Offset] >= lastyearoffset - 9)

)

RETURN

DIVIDE(lastyearstotal,10)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

Hi, 

Thanks for the feedback.

I'm still with the same issue as I had before.

I need the value of 2,3m statics even though when Year slicer is selected for a specific range of years.

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

I do not know the reason why you created the page level filter.

But in order to meet your request, I needed to delete your page-level filter and put those into the measure itself. 

When you are applying this to your own model, please try to delete the page level filter and put those into the measure like below.

Please check the link below.

_Take Up - 10 Years Average2 =
VAR lastyearoffset =
CALCULATE ( MAX ( dim_Date[Year Offset] ), ALL ( dim_Date ) )
VAR lastyearstotal =
CALCULATE (
SUM ( fct_10YR_JLLREDS_Demand[Area Leased sqm] ),
FILTER (
ALL ( dim_Date ),
dim_Date[Year Offset] >= lastyearoffset - 9
&& dim_Date[Not future date] = 1
)
)
RETURN
IF (
NOT ISBLANK ( SUM ( fct_10YR_JLLREDS_Demand[Area Leased sqm] ) ),
DIVIDE ( lastyearstotal, 10 )
)

https://www.dropbox.com/s/z9pzvg3wowcthuv/fabtest.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

@Hi, @Jihwan_Kim 

Thanks for your solution. It's working now

The reason I put that filter on the page level is to make my life easier as in the original dashboard I have many pages and measures that contain values with no dates and I don't want to display them in the visuals.

thank you!

[amitchandak]

@Anonymous , check if one of the two options (for a new measure can help)

averageX(summarize(allselected(Table), Table[year offset], Table[Year] , "_1", [Amount]),[_1])

calculate(averageX(values(Table[year offset]) , [Amount]),allselected(Table))