Forum Discussion
Creating Bell Graph responsive to slicers for Total Learning Hours per Employee
Hello all,
one of the "must have" requested by my report consumers is to create a Bell Chart that will show Distribution of total learning hours by unique employees (ID) that would be responsive to the slicer for Fiscal Year, Quarter, Month.
Total Lerning Hours should be categorized in buckets 0-10, 10-20, ... 100-110, and so on in ascending order.
I do have a respective Calendar Table for Fiscal Year Calucaltions since the starting month for fiscal year is September.
Relationship is set as follows and for all other used visualisations/measures works fine.
The following is sample of FACT Data table.
'FACT_report'
| GID | Status | Learning Activity ID | Start Date | End Date | Duration Hours |
| TM01 | Completed | 88233017 | 01/13/2024 | 02/13/2023 | 1,00 |
| TM01 | Completed | 90432215 | 06/01/2023 | 06/01/2023 | 1,50 |
| TM01 | Completed | 90711675 | 06/14/2023 | 06/14/2023 | 1,50 |
| TM01 | Completed | 90709625 | 06/14/2023 | 06/14/2023 | 1,50 |
| TM01 | Completed | 91145570 | 06/29/2023 | 06/29/2023 | 1,50 |
| TM01 | In Progress | 91469486 | 07/12/2023 | 07/12/2023 | 1,50 |
| TM01 | Canceled | 93311029 | 07/19/2023 | 07/20/2023 | 0,67 |
| TM02 | Completed | 90452739 | 06/02/2023 | 06/02/2023 | 5,00 |
| TM02 | Completed | 93255545 | 06/06/2023 | 06/06/2023 | 0,50 |
| TM02 | Completed | 93255546 | 06/06/2023 | 06/06/2023 | 0,50 |
| TM02 | Completed | 90796163 | 06/16/2023 | 06/16/2023 | 0,67 |
| TM02 | Completed | 90859935 | 06/20/2023 | 06/20/2023 | 0,25 |
| TM03 | Completed | 87781501 | 01/05/2024 | 01/19/2023 | 0,42 |
| TM03 | Completed | 87901168 | 01/03/2024 | 01/26/2023 | 0,83 |
| TM03 | Completed | 88204942 | 02/09/2023 | 02/09/2023 | 0,50 |
| TM03 | Completed | 88318969 | 02/10/2023 | 02/10/2023 | 0,67 |
| TM03 | Completed | 88606510 | 03/01/2023 | 03/01/2023 | 0,50 |
| TM03 | Completed | 88661196 | 03/02/2023 | 03/02/2023 | 0,50 |
| TM03 | In Progress | 88768962 | 03/07/2023 | 03/07/2023 | 1,50 |
| TM03 | In Progress | 88877707 | 03/15/2023 | 03/15/2023 | 0,75 |
| TM03 | Completed | 89037314 | 03/21/2023 | 03/21/2023 | 2,00 |
| TM03 | Completed | 89849020 | 04/17/2023 | 04/17/2023 | 0,83 |
| TM03 | Completed | 89770016 | 04/24/2023 | 04/24/2023 | 1,50 |
| TM03 | Completed | 89847320 | 04/27/2023 | 04/27/2023 | 0,83 |
| TM03 | Completed | 89990469 | 05/04/2023 | 05/04/2023 | 1,00 |
| TM04 | Completed | 89134843 | 01/01/2024 | 02/01/2023 | 0,50 |
| TM04 | Completed | 88266501 | 02/15/2023 | 02/15/2023 | 1,00 |
| TM04 | Completed | 89339230 | 03/06/2023 | 03/06/2023 | 2,00 |
| TM04 | Completed | 88870217 | 03/14/2023 | 03/14/2023 | 1,17 |
| TM04 | Completed | 89105593 | 03/15/2023 | 03/15/2023 | 0,67 |
| TM05 | Completed | 89394912 | 04/03/2023 | 04/03/2023 | 1,67 |
| TM05 | In Progress | 89652109 | 04/11/2023 | 04/11/2023 | 0,67 |
| TM05 | Completed | 90215824 | 05/18/2023 | 05/18/2023 | 6,00 |
| TM05 | Completed | 91163274 | 06/01/2023 | 06/30/2023 | 2,33 |
| TM05 | Completed | 93534670 | 07/01/2023 | 07/31/2023 | 0,50 |
| TM05 | Completed | 94279005 | 07/18/2023 | 08/15/2023 | 10,00 |
| TM05 | Completed | 95120625 | 08/01/2023 | 08/31/2023 | 0,50 |
| TM05 | Completed | 95133148 | 09/12/2023 | 09/12/2023 | 2,00 |
| TM05 | Completed | 95775505 | 09/21/2023 | 09/22/2023 | 16,00 |
| TM05 | Completed | 96520067 | 10/02/2023 | 10/05/2023 | 16,00 |
| TM05 | Completed | 98950811 | 11/02/2023 | 11/02/2023 | 0,67 |
| TM05 | Completed | 101818488 | 12/20/2023 | 12/20/2023 | 0,50 |
The outcome should be this:
X Axis - buckets of Total Learning Hours, Min "0-10", Max "400+", Increment 10
Y Axis - Number of unique employee ID
Only Learnings in status Completed Should be taken into the account.
I have tried to follow this approach https://radacad.com/customers-grouped-by-the-count-of-their-orders-dynamic-segmentation-in-power-bi-using-dax-measures however this is quite advanced for me and not giving me the results similar to above picture.
Appreaciate if you can support me how to achive the requested outcome. Unfortunately, checking with several internet sources doesn´t provide the instructions I would be able to follow to achieve this.
Thank you!
4 Replies
Hi Jayleny,
unfortuantely, I am not able to attach .pbix file here, the option is not enabled for me.
Sample data are below.
In addition I have created a calculated column in PBI (code below), but it is static calculation. I need it to be dynamic based on the filtering.
For example:
User Z004F80T for unfiltered data falls under category 60-70, but for filtered data Fiscal year 2024 (meaning October 2023-September 2024) he should fall under category 0-10.
The result should have outcome in this visualization:CALCULATED COLUMN CODE:
CategoryAssigned =VAR TotalHours = CALCULATE(SUM('FACT_Report'[Duration hours]),ALLEXCEPT(FACT_Report,FACT_Report[GID]))RETURNSWITCH(TRUE(),TotalHours >= 0 && TotalHours < 10, "0-10",TotalHours >= 10 && TotalHours < 20, "10-20",TotalHours >= 20 && TotalHours < 30, "20-30",TotalHours >= 30 && TotalHours < 40, "30-40",TotalHours >= 40 && TotalHours < 50, "40-50",TotalHours >= 50 && TotalHours < 60, "50-60",TotalHours >= 60 && TotalHours < 70, "60-70",TotalHours >= 70 && TotalHours < 80, "70-80",TotalHours >= 80 && TotalHours < 90, "80-90",TotalHours >= 90 && TotalHours < 100, "90-100",TotalHours >= 100 && TotalHours < 110, "100-110",TotalHours >= 110 && TotalHours < 120, "110-120",TotalHours >= 120 && TotalHours < 130, "120-130",TotalHours >= 130 && TotalHours < 140, "130-140",TotalHours >= 140 && TotalHours < 150, "140-150",TotalHours >= 150 && TotalHours < 160, "150-160",TotalHours >= 160 && TotalHours < 170, "160-170",TotalHours >= 170 && TotalHours < 180, "170-180",TotalHours >= 180 && TotalHours < 190, "180-190",TotalHours >= 190 && TotalHours < 200, "190-200",TotalHours >= 200 && TotalHours < 210, "200-210",TotalHours >= 210 && TotalHours < 220, "210-220",TotalHours >= 220 && TotalHours < 230, "220-230",TotalHours >= 230 && TotalHours < 240, "230-240",TotalHours >= 240 && TotalHours < 250, "240-250",TotalHours >= 250 && TotalHours < 260, "250-260",TotalHours >= 260 && TotalHours < 270, "260-270",TotalHours >= 270 && TotalHours < 280, "270-280",TotalHours >= 280 && TotalHours < 290, "280-290",TotalHours >= 290 && TotalHours < 300, "290-300",TotalHours >= 300 && TotalHours < 310, "300-310",TotalHours >= 310 && TotalHours < 320, "310-320",TotalHours >= 320 && TotalHours < 330, "320-330",TotalHours >= 330 && TotalHours < 340, "330-340",TotalHours >= 340 && TotalHours < 350, "340-350",TotalHours >= 350 && TotalHours < 360, "350-360",TotalHours >= 360 && TotalHours < 370, "360-370",TotalHours >= 370 && TotalHours < 380, "370-380",TotalHours >= 380 && TotalHours < 390, "380-390",TotalHours >= 390 && TotalHours < 400, "390-400",TotalHours >= 400, "400+","N/A")
Sample data:
Content Category (Level 1) GID OrgCode Location Status Start Date Duration Hours Interpersonal & Personal Z004F80T Department25 PRG S Completed 05/01/2023 0,25 Technology & Market Z004F80T Department25 PRG S Completed 05/03/2023 10,45 Leadership Z004F80T Department25 PRG S Completed 05/03/2023 1,86 Technology & Market Z004F80T Department25 PRG S Completed 05/05/2023 17,67 Technology & Market Z004F80T Department25 PRG S Completed 05/06/2023 12,31 Technology & Market Z004F80T Department25 PRG S Completed 05/14/2023 10,99 Technology & Market Z004F80T Department25 PRG S Completed 05/22/2023 1,25 Function & Methods Z004F80T Department25 PRG S Completed 05/22/2023 0,20 Function & Methods Z004F80T Department25 PRG S Completed 06/29/2023 0,50 Interpersonal & Personal Z004F80T Department25 PRG S Completed 08/20/2023 0,25 Leadership Z004F80T Department25 PRG S Completed 08/20/2023 0,17 Interpersonal & Personal Z004F80T Department25 PRG S Completed 08/20/2023 0,25 Technology & Market Z004F80T Department25 PRG S Completed 08/20/2023 0,25 Leadership Z004F80T Department25 PRG S Completed 09/05/2023 0,25 Leadership Z004F80T Department25 PRG S Completed 09/05/2023 0,25 Leadership Z004F80T Department25 PRG S Completed 09/05/2023 0,25 Interpersonal & Personal Z004F80T Department25 PRG S Completed 09/19/2023 0,25 Interpersonal & Personal Z004F80T Department25 PRG S Completed 10/03/2023 0,37 Technology & Market Z004F80T Department25 PRG S Completed 10/16/2023 0,13 Function & Methods Z004F80T Department25 PRG S Completed 10/19/2023 1,00 Function & Methods Z004F80T Department25 PRG S Completed 10/26/2023 0,67 Technology & Market Z004F80T Department25 PRG S Completed 10/28/2023 0,38 Interpersonal & Personal Z004F80T Department25 PRG S Completed 11/20/2023 0,25 Interpersonal & Personal Z004F80T Department25 PRG S Completed 11/20/2023 0,25 Interpersonal & Personal Z004F80T Department25 PRG S Completed 11/20/2023 0,25 Interpersonal & Personal Z004F80T Department25 PRG S Completed 11/20/2023 0,25 Interpersonal & Personal Z004F80T Department25 PRG S Completed 11/20/2023 0,25 Interpersonal & Personal Z004F80T Department25 PRG S Completed 11/20/2023 0,25 Interpersonal & Personal Z004F80T Department25 PRG S Completed 11/20/2023 0,25 Interpersonal & Personal Z004F80T Department25 PRG S Completed 11/20/2023 0,25 Interpersonal & Personal Z004F80T Department25 PRG S Completed 11/20/2023 0,25 Leadership Z004F80T Department25 PRG S Completed 11/20/2023 0,25 Function & Methods Z004F80T Department25 PRG S Completed 12/22/2023 0,50 Function & Methods Z004F80T Department25 PRG S Completed 01/12/2024 1,00 Function & Methods Z004F80T Department25 PRG S Completed 01/29/2024 0,60