Average of min start time by salesman by day by BUID

[SmailBelkhiri]

Hello everyone

i will be greatful if you can help me with my issue

i have a fact table , with SalesmanId , Date, BUID , and startime.

i want to know the average of start time per day by salesnan , or BUID

my table is as below :

salesmanID	dateid	BUID	nb plan	Starttime
VP09-0003	04-02-2024	11010302	41	11:15
VP09-0003	05-02-2024	1101 0302	46	10:36
VP09-0003	06-02-2024	11010302	42	11:06
VP09-0007	04-02-2024	11010302	39	10:56
VP09-0007	05-02-2024	11010302	43	10:58
VP09-0007	06-02-2024	11010302	35	10:55
V090001	04-02-2024	11010303	33	10:11
V090001	05-02-2024	11010303	12	7:20
V090001	06-02-2024	11010303	10	10:33
V090003	04-02-2024	11010303	72	10:41
V090003	05-02-2024	11010303	53	1:19
V090003	06-02-2024	11010303	74	11:32
VP09-0001	04-02-2024	11010303	45	10:50
VP09-0001	05-02-2024	11010303	32	10:48
VP09-0001	06-02-2024	11010303	53	10:31

the result have to be like this :

salesmanID	AVERAGE START TIME
VP09-0003	10:59
VP09-0007	10:57
V090001	9:21
V090003	7:51
VP09-0001	10:43

or by BUId like this :

BUID	AVERAGE START TIME
11010302	10:58
11010303	9:18

[SmailBelkhiri]

@v-zhengdxu-msft   After rechecking , the measure dont give me the average of minimum , if i have many  days , the measure give me the average , not the average of just the min time by day 

[v-zhengdxu-msft]

Hi @SmailBelkhiri 

Does the data type of your Start time column be Time or Date/time?

^{Best Regards,}

^{Zhengdong Xu}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

[SmailBelkhiri]

Date time 

[v-zhengdxu-msft]

Hi @SmailBelkhiri 

Please try this:

Measure 2 = 
    FORMAT(
		AVERAGEX(
			'Table',
			TIME(
				HOUR('Table'[Starttime]),
				MINUTE('Table'[Starttime]),
				SECOND('Table'[Starttime])
			)
		),
		"hh:nn:ss"
	)

^{Best Regards,}

^{Zhengdong Xu}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

[SmailBelkhiri]

@v-zhengdxu-msft   thats give us , the average , not the average of min time 😕

[v-zhengdxu-msft]

Hi @SmailBelkhiri 

Wait...what?

Don't you need an average of the start time Or I missed something?

What does the min time mean?

[SmailBelkhiri]

EXCEL FILE 

CHECK THIS

[SmailBelkhiri]

thank you @v-zhengdxu-msft 

[v-zhengdxu-msft · Accepted solution]

Hi @SmailBelkhiri 

Please try this:

Measure = 
VAR _currentID = SELECTEDVALUE('Table'[salesmanID])
RETURN CALCULATE(
            FORMAT(
                AVERAGE('Table'[Starttime]),
                "hh:nn:ss"
            ),
            FILTER(
                ALLSELECTED('Table'),
                'Table'[salesmanID]=_currentID
            )
        )

The result is as follow:

It's worth methoding that the value of the measure returned is the text but time.

This link may help you better:

How to calcualte average of time columns in Power BI (DAX) | by Lukas Lötters | Medium

^{Best Regards,}

^{Zhengdong Xu}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

[SmailBelkhiri]

Hi @v-zhengdxu-msft 

thank you for helping me , the measure work perfectly with Salesman , but it doesn't work with BUID .

thank you

[v-zhengdxu-msft]

Hi @SmailBelkhiri 

Just change the SalesmanID into BUID: 

Measure = 
VAR _currentID = SELECTEDVALUE('Table'[BUID])
RETURN CALCULATE(
            FORMAT(
                AVERAGE('Table'[Starttime]),
                "hh:nn:ss"
            ),
            FILTER(
                ALLSELECTED('Table'),
                'Table'[BUID]=_currentID
            )
        )

The result is as follow:

^{Best Regards,}

^{Zhengdong Xu}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

[SmailBelkhiri]

@v-zhengdxu-msft 

Does it works with both , salesman & BUID  ? or i should to create measure for each one ?

Thanks

[v-zhengdxu-msft]

Hi @SmailBelkhiri 

Try this:

Measure 2 = 
    FORMAT(
        AVERAGE('Table'[Starttime]),
        "hh:nn:ss"
        )

This one works, but you need to create visuals for each one like this:

^{Best Regards,}

^{Zhengdong Xu}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}