Forum Discussion
Calculate mode from a data table
Nice day.
I have a historical data table with which I have calculated the average and median but I lack fashion. I've been searching different forums and haven't found the solution. I would be very grateful for your recommendations.
Attached part of the table that is required to calculate the time mode for each sub activity:
| SUB ACTIVITY | TIME |
| Contracts, agreements and legal documents | 0:10:00 |
| Contracts, agreements and legal documents | 0:10:00 |
| Meeting with supplier | 0:15:00 |
| Payment process | 0:15:00 |
| Quote | 0:15:00 |
| Payment process | 0:15:00 |
| Review and filling of WBS, requisitions | 0:15:00 |
| Contracts, agreements and legal documents | 0:15:00 |
| Quote | 0:15:00 |
| Contracts, agreements and legal documents | 0:30:00 |
| Contracts, agreements and legal documents | 0:30:00 |
| Purchase Order | 0:30:00 |
| Quote | 0:30:00 |
| Validation and specification | 0:30:00 |
| Modify Ms Project program | 1:15:00 |
| Ms Project Program Review / Activities | 1:30:00 |
| Ms Project Program Review | 3:30:00 |
| Design/draw/model | 5:40:00 |
| Purchase Order | 0:15:00 |
| Purchase Order | 0:15:00 |
| Purchase Order | 0:15:00 |
| Guarantees | 0:15:00 |
| Guarantees | 0:15:00 |
| Validation and specification | 0:15:00 |
| Purchase Order | 0:20:00 |
| Purchase Order | 0:20:00 |
| Purchase Order | 0:20:00 |
| Payment process | 0:30:00 |
| Comparative | 0:45:00 |
| Validation and specification | 0:45:00 |
| Validation and specification | 0:45:00 |
| Updating indicators | 1:00:00 |
| Validation and specification | 1:30:00 |
| Quote | 1:30:00 |
| Updating indicators | 1:30:00 |
| Ms Project Program Review | 2:45:00 |
| Ms Project Program Review | 3:00:00 |
| Printing/Folding Drawings | 4:00:00 |
| Plano as-built | 4:00:00 |
| Architectural program and zoning | 8:00:00 |
| Design/draw/model | 8:00:00 |
| Quantification/Catalog/WBS | 8:00:00 |
| Design/draw/model | 9:00:00 |
| Design/draw/model | 9:00:00 |
| Quote | 0:15:00 |
| Contracts, agreements and legal documents | 0:15:00 |
| Purchase Order | 0:15:00 |
| Purchase Order | 0:15:00 |
| Purchase Order | 0:15:00 |
| Payment process | 0:15:00 |
| Job closure | 0:20:00 |
| Job closure | 0:25:00 |
| Quote | 0:25:00 |
| Quote | 0:25:00 |
| Payment process | 0:30:00 |
| Comparative | 0:30:00 |
| Quote | 0:30:00 |
| Contracts, agreements and legal documents | 0:35:00 |
| Comparative | 0:40:00 |
| Validation and specification | 0:45:00 |
| Contracts, agreements and legal documents | 0:45:00 |
| Quote | 1:00:00 |
| Validation and specification | 1:00:00 |
| Construction route | 1:00:00 |
| CONSTRUCTION SUPERVISION | 2:00:00 |
| Development of generators and volumetries | 2:00:00 |
| ELABORATION OF MATERIAL REQUIREMENTS | 2:00:00 |
| Site visit | 2:00:00 |
| Purchase tracking | 2:00:00 |
| DELIVERY TO OPERATION | 3:00:00 |
| Printing/Folding Drawings | 3:00:00 |
| DELIVERY TO OPERATION | 5:00:00 |
| Project request (interiors/engineering) | 5:00:00 |
| Design/draw/model | 5:00:00 |
| Design bases/criteria | 5:00:00 |
| Design/draw/model | 7:00:00 |
| Review and filling of WBS, requisitions | 0:05:00 |
| Review and filling of WBS, requisitions | 0:05:00 |
| Review and filling of WBS, requisitions | 0:05:00 |
| Review and filling of WBS, requisitions | 0:05:00 |
| Review and filling of WBS, requisitions | 0:05:00 |
| Review and filling of WBS, requisitions | 0:05:00 |
| Review and filling of WBS, requisitions | 0:05:00 |
| Review and filling of WBS, requisitions | 0:05:00 |
| Review and filling of WBS, requisitions | 0:05:00 |
| Review and filling of WBS, requisitions | 0:05:00 |
| Quote | 0:10:00 |
| Quote | 0:10:00 |
| Quote | 0:10:00 |
| Quote | 0:10:00 |
Thank you.
You just can remove the column I have put in measure, than the output is correct
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Syndicate_Admin , Based on what I got. You have convert time to second or minute and take avg
seconds = hour([Time])*3600 + minute([Time])*3600 + Second([Time])
You can use median or medianx
median measure= medianx(Table, Table[second])
if needed convert back to time
time(0,0,0) + [median measure]/3600*24
Thank you amitchandak, I understand that this solution you propose gives as an answer to the median.
But the data I need is fashion, which is the value that is most repeated.
Best regards.
the following is my sample,
1. create a new column to transfer the time to minutes
Minutes_TIME = HOUR('Table'[TIME])*60+MINUTE('Table'[TIME])2.Then create two measures:
AVG_TIME = AVERAGEX(FILTER(ALL('Table'),[SUB ACTIVITY]=MAX('Table'[SUB ACTIVITY])),[Minutes_TIME]) MED_TIME = MEDIANX(FILTER(ALL('Table'),[SUB ACTIVITY]=MAX('Table'[SUB ACTIVITY])),[Minutes_TIME])Output:
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you Yolo Zhu, I understand that this solution you propose gives as a response to the average and the median.
But the data I need is fashion, which is the value that is most repeated.
Best regards.
You just can remove the column I have put in measure, than the output is correct
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
As a complement to the help I require, it is like having a table with the following data:
Nationality Age Mexico 25 Mexico 15 Mexico 30 Mexico 30 Mexico 20 Colombia 22 Colombia 35 Colombia 40 Colombia 40 Colombia 50 Argentina 18 Argentina 21 Argentina 45 Argentina 45 Argentina 33 Canada 16 Canada 22 Canada 28 Canada 28 Canada 34 And that as a result the following summary table:
Nationality Fashion (age) Mexico 30 Colombia 40 Argentina 45 Canada 28
