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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors