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

Get 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

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
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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