Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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 |
Solved! Go to Solution.
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.
@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
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
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.
Wait...what?
Don't you need an average of the start time Or I missed something?
What does the min time mean?
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.
thank you for helping me , the measure work perfectly with Salesman , but it doesn't work with BUID .
thank you
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.
Does it works with both , salesman & BUID ? or i should to create measure for each one ?
Thanks
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
76 | |
74 | |
55 | |
45 |
User | Count |
---|---|
117 | |
105 | |
77 | |
66 | |
64 |