Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hello,
I tired do a calculated table where in every row is the total average of its own categeroy for orders which are status = "Done".
The average column is what I want to calculate. The respective row always shows the total average value of its category and Status needs to be "Done":
OrderID | Category | Status | Seconds | Average | ||||
1234 | Fetch | Done | 100 | 168,333333 | ||||
1235 | Fetch | Done | 150 | 168,333333 | ||||
1236 | Fetch | Done | 200 | 168,333333 | ||||
1237 | Fetch | Done | 200 | 168,333333 | ||||
1238 | Fetch | Done | 300 | 168,333333 | ||||
1239 | Fetch | Done | 60 | 168,333333 | ||||
1240 | Move | Done | 100 | 83,3333333 | ||||
1241 | Move | Done | 50 | 83,3333333 | ||||
1242 | Move | Done | 100 | 83,3333333 | ||||
1243 | Move | Fail | 50 | 0 |
I tried several approaches with summarize , addcolumns and Filter (All... , but I always get the seconds of its own row.
Thank you very much in advance.
Best.
OrderID | Category | Seconds | Average | |||
1234 | Fetch | 100 | 168,333333 | |||
1235 | Fetch | 150 | 168,333333 | |||
1236 | Fetch | 200 | 168,333333 | |||
1237 | Fetch | 200 | 168,333333 | |||
1238 | Fetch | 300 | 168,333333 | |||
1239 | Fetch | 60 | 168,333333 | |||
1240 | Move | 100 | 75 | |||
1241 | Move | 50 | 75 | |||
1242 | Move | 100 | 75 | |||
1243 | Move | 50 | 75 |
Solved! Go to Solution.
If I hot your point corrctly, try this code to add a new column:
Average =
CALCULATE( AVERAGE( 'Table'[Seconds] ), ALLEXCEPT( 'Table', 'Table'[Category] ))
output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
Hi,
This calculated column formula works
Average = if(Data[Status]="Done",CALCULATE(AVERAGE(Data[Seconds]),FILTER(Data,Data[Category]=EARLIER(Data[Category])&&Data[Status]="Done")),0)
Hope this helps.
Hi,
This calculated column formula works
Average = if(Data[Status]="Done",CALCULATE(AVERAGE(Data[Seconds]),FILTER(Data,Data[Category]=EARLIER(Data[Category])&&Data[Status]="Done")),0)
Hope this helps.
Which one of those tables you shares is the result and which one is the sample data? Can you add more details?
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
@VahidDM , thanks for the quick reply. First three columns are sample data. Column "Average" is my preferred outcome.
If I hot your point corrctly, try this code to add a new column:
Average =
CALCULATE( AVERAGE( 'Table'[Seconds] ), ALLEXCEPT( 'Table', 'Table'[Category] ))
output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
85 | |
84 | |
67 | |
49 |
User | Count |
---|---|
131 | |
111 | |
97 | |
71 | |
67 |