Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
yogeshk77
Helper I
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 ->

1.jpg

 

The output should be like this ->

 

2.jpg

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
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:

 

vyangliumsft_0-1695382910572.png

 

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

View solution in original post

4 REPLIES 4
v-yangliu-msft
Community Support
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:

 

vyangliumsft_0-1695382910572.png

 

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

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

 

yogeshk77_0-1695817342435.png

 

yogeshk77
Helper I
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"

 

Greg_Deckler
Super User
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 = 
    ADDCOLUMNS(
      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.



Follow on LinkedIn
@ 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...

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

October NL Carousel

Fabric Community Update - October 2024

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

Top Solution Authors