Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
108 | |
108 | |
93 | |
61 |
User | Count |
---|---|
169 | |
138 | |
135 | |
102 | |
86 |