Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
SmailBelkhiri
Regular Visitor

Average of min start time by salesman by day by BUID

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 :

salesmanIDdateidBUIDnb planStarttime
VP09-000304-02-2024110103024111:15
VP09-000305-02-2024

1101

0302

4610:36
VP09-000306-02-2024110103024211:06
VP09-000704-02-2024110103023910:56
VP09-000705-02-2024110103024310:58
VP09-000706-02-2024110103023510:55
V09000104-02-2024110103033310:11
V09000105-02-202411010303127:20
V09000106-02-2024110103031010:33
V09000304-02-2024110103037210:41
V09000305-02-202411010303531:19
V09000306-02-2024110103037411:32
VP09-000104-02-2024110103034510:50
VP09-000105-02-2024110103033210:48
VP09-000106-02-2024110103035310:31

 

the result have to be like this :

salesmanIDAVERAGE START TIME
VP09-000310:59
VP09-000710:57
V0900019:21
V0900037:51
VP09-000110:43

 

or by BUId like this :

BUIDAVERAGE START TIME
1101030210:58
11010303

9:18

1 ACCEPTED SOLUTION
v-zhengdxu-msft
Community Support
Community Support

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:

vzhengdxumsft_0-1707798640766.png

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.

View solution in original post

13 REPLIES 13
SmailBelkhiri
Regular Visitor

@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 

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.

Date time 

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.

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

Hi @SmailBelkhiri 

 

Wait...what?

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

What does the min time mean?

 

EXCEL FILE 

CHECK THIS

SmailBelkhiri
Regular Visitor

thank you @v-zhengdxu-msft 

v-zhengdxu-msft
Community Support
Community Support

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:

vzhengdxumsft_0-1707798640766.png

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.

Hi @v-zhengdxu-msft 

 

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

thank you

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:

vzhengdxumsft_0-1707811634270.png

 

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.

 

@v-zhengdxu-msft 

 

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

 

Thanks

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:

vzhengdxumsft_0-1707812350839.png

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors