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
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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