March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet 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
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 ->
Solved! Go to Solution.
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
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
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
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"
@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.
User | Count |
---|---|
31 | |
19 | |
15 | |
14 | |
10 |
User | Count |
---|---|
62 | |
25 | |
24 | |
22 | |
16 |