Calculate mode from a data table

[Syndicate_Admin]

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.

[Syndicate_Admin]

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

[Anonymous]

Hi @Syndicate_Admin 

 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.

[Syndicate_Admin]

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.

[Anonymous · Accepted solution]

Hi @Syndicate_Admin 

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.

[amitchandak]

@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

[Syndicate_Admin]

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.