Forum Discussion
Hire Duration
Hi All,
I have a dataset similar to the below
| Task ID | Task Owner | Hire Duration (Days) |
| 1 | Bob | 10 |
| 2 | Charlie | 100 |
| 3 | Charlotte | 200 |
| 4 | Andy | 400 |
I want to create a bar graph showing task owners (x axis) with task count of task ID's above below thresholds
<90 days
>90 days & <180 days
>180 days & <360 days
>360 days
Ideally each task owner will have a column within the bar of the bar chart then a colour coded system allocated to ID's on hire using the above criteria.
Any ideas on how this can be achieved?
Many thanks,
Elliot
Hi,
Thank you for your feedback.
I am not sure if I understood your question correctly, but please check the below.
Task count : =
VAR _newtable =
FILTER (
ADDCOLUMNS (
VALUES ( Data[Task ID] ),
"@duration", CALCULATE ( SUM ( Data[Hire Duration (Days)] ) )
),
[@duration] >= SELECTEDVALUE ( 'Group'[Min] )
&& [@duration] < SELECTEDVALUE ( 'Group'[Max] )
)
RETURN
COUNTROWS ( _newtable )Hi, please check the above link.
- you can try to sort the group-column by MIN column. -> I re-arranged it.
- you need to create an additional measure to define the total duration for each owner. However, the duration total measure may not show in the visualization, so one way to solve this is to add this additional measure into the tooltip, then you can click the three dots on the upper right corner of the visualization, then click sort by the additional measure.
I hope it helps.
Thanks.
7 Replies
Hey Anonymous ,
you can do that with a calculated column.
Add a column with the following formula:
Hire Group = SWITCH( TRUE(), myTable[Hire Duration] < 90, "<90 days", myTable[Hire Duration] < 180, ">90 days & <180 days", myTable[Hire Duration] < 360, ">180 days & <360 days", ">360 days" )This column you can then use in your analysis.
If you need any help please let me know.If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍Best regardsDenisBlog: WhatTheFact.biFollow me: twitter.com/DenSelimovicHi,
Thanks for your quick response. I have created this custom column and added it into my bar chart visual (hire group in x axis & task owner in y axis).
The only options for displaying this data are count of hire group. This is not displaying each task owner with a count of ID's within each group.
Any ideas?
Thanks,
Elliot
Task count : =
VAR _duration =
SUM ( Data[Hire Duration (Days)] )
RETURN
CALCULATE (
COUNTROWS ( Data ),
FILTER (
VALUES ( Owners[Task Owner] ),
COUNTROWS (
FILTER ( 'Group', _duration >= 'Group'[Min] && _duration < 'Group'[Max] )
) > 0
)
)Hi,
I have tried this but are still experiencing some problems.
Please see the attached PBIX file. I have added more tasks to 'Andy' and the bar chart is not showing 'Andy' has ID's with hire duration in all catagories
Idealy Andy should now read having ID's in 3 out of 4 catagories.
Kind regards,
Elliot
https://www.dropbox.com/s/xt5zmlrj8qam8dj/serdet.pbix?dl=0
Kind regards,
Hi,
Thank you for your feedback.
I am not sure if I understood your question correctly, but please check the below.
Task count : =
VAR _newtable =
FILTER (
ADDCOLUMNS (
VALUES ( Data[Task ID] ),
"@duration", CALCULATE ( SUM ( Data[Hire Duration (Days)] ) )
),
[@duration] >= SELECTEDVALUE ( 'Group'[Min] )
&& [@duration] < SELECTEDVALUE ( 'Group'[Max] )
)
RETURN
COUNTROWS ( _newtable )
