Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
So im really having a hard time with making an average of the last 10 values excluding the blanks. the average must take into consideration that the blanks inbetween the values are inconsistent. I hope you get the picture. I used the command below but it only copied the values instead of averaging them:
Before, I used to edit the formula bar each week in Excel to update the values so it would only average the last 10 values like this:
If anyone could help out that would be great!
thank you
Solved! Go to Solution.
Hi,
Please check the below picture and the attached pbix file.
I tried to create a sample pbix file like the attached file.
Last 10 CZV avg measure: =
VAR newtable =
TOPN (
10,
FILTER ( ALL ( Data ), NOT ISBLANK ( Data[CZV] ) ),
Data[Date], DESC
)
RETURN
IF (
SELECTEDVALUE ( Data[Date] ) >= MINX ( newtable, Data[Date] )
&& SELECTEDVALUE ( Data[Date] ) <= MAXX ( newtable, Data[Date] )
&& NOT ISBLANK ( [CZV total measure:] ),
AVERAGEX ( newtable, Data[CZV] )
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Try replacing VALUES ( 'afvalwater CPL'[Datum] ) with ALLSELECTED ( 'afvalwater CPL'[Datum] )
This didnt work. gave me duplicate values instead. thank you tho!
Hi,
Please check the below picture and the attached pbix file.
I tried to create a sample pbix file like the attached file.
Last 10 CZV avg measure: =
VAR newtable =
TOPN (
10,
FILTER ( ALL ( Data ), NOT ISBLANK ( Data[CZV] ) ),
Data[Date], DESC
)
RETURN
IF (
SELECTEDVALUE ( Data[Date] ) >= MINX ( newtable, Data[Date] )
&& SELECTEDVALUE ( Data[Date] ) <= MAXX ( newtable, Data[Date] )
&& NOT ISBLANK ( [CZV total measure:] ),
AVERAGEX ( newtable, Data[CZV] )
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Thanks a bunch! this worked for me.
User | Count |
---|---|
60 | |
22 | |
18 | |
18 | |
16 |
User | Count |
---|---|
86 | |
54 | |
54 | |
38 | |
21 |