cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

Conditional Rolling Average

I have below mentioned Power BI table structure Table

WeightedVel table contains below columns

“Team_10202” as a String Data type

“Reporting Sprint ID” as an Integer Data type

“Story_Points_10004” as a Decimal Data type

Now, I need a calculated column in this table to get average of last six “Story_Points_10004”, ordered by “Reporting Sprint ID”, grouped by “Team_10202”
Ensure you include the value of the current row in this calculation

For e.g. if the data is like this ->

The output should be like this ->

1 ACCEPTED SOLUTION
Community Support

Hi  @yogeshk77 ,

Here are the steps you can follow：

1. Create calculated column.

rank =
RANKX(
FILTER(ALL(Expected),
'Expected'[Team]=EARLIER('Expected'[Team])),[Reporting Sprint ID],,DESC)
Sum of Six Period Average =
IF(
[rank] <=6,
AVERAGEX(
FILTER(ALL('Expected'),
'Expected'[Team]=EARLIER('Expected'[Team])&&
'Expected'[rank]>=EARLIER('Expected'[rank])&&'Expected'[rank]<=EARLIER('Expected'[rank])+5),[Story_Points])

)

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

4 REPLIES 4
Community Support

Hi  @yogeshk77 ,

Here are the steps you can follow：

1. Create calculated column.

rank =
RANKX(
FILTER(ALL(Expected),
'Expected'[Team]=EARLIER('Expected'[Team])),[Reporting Sprint ID],,DESC)
Sum of Six Period Average =
IF(
[rank] <=6,
AVERAGEX(
FILTER(ALL('Expected'),
'Expected'[Team]=EARLIER('Expected'[Team])&&
'Expected'[rank]>=EARLIER('Expected'[rank])&&'Expected'[rank]<=EARLIER('Expected'[rank])+5),[Story_Points])

)

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Helper I

Thank you.. In the stand alone test PBI this works, but in practical I have multiple other teams and looks like due to that combination the rank function is not working as expected.

Here's the result from my actual PBI

Helper I

You can check this file

I Need a calculated column in the table called "Data", which shows the output in the below table called "Expected Output"

Super User

@yogeshk77 Should be:

Column =
VAR __Team = [Learnosity_Team_10202]
VAR __Sprint = [Reporting Sprint ID]
VAR __Sprints = SELECTCOLUMNS(FILTER('Table', [Learnosity_Team_10202] = __Team && [Reporting Sprint ID] <= __Sprint), "__SprintID", [Reporting Sprint ID])
VAR __SprintsText = CONCATENATEX( __Sprints, [__SprintID], "|", [__SprintID], DESC)
VAR __Table =
GENERATESERIES( 1, COUNTROWS(__Sprints), 1 ),
"__SprintID", PATHITEM(__SprintsText, [Value])
)
VAR __FinalSprints = FILTER(__Table, [Value] <= 6)
VAR __Result = AVERAGEX( FILTER('Table', [Reporting Sprint ID] IN __FinalSprints), [Sum of Story_Points_10004)
RETURN
__Result

Quite possible that you will get some kind of error complaining about text vs. number. Also, couldn't test this so the probability of a syntax error is fairly high. If you provide your sample data as text, could test this in a PBIX file and provide the PBIX file to you.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors