Creating Bell Graph responsive to slicers for Total Learning Hours per Employee

[Marketa01]

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-... 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!

[Marketa01]

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

[Anonymous]

Hi @Marketa01 

Can you provide detailed sample pbix file and the results you expect.So that I can help you better. Please remove any sensitive data in advance.

Best Regards,

Jayleny

[Marketa01]

Hello Jayleny,
any help with the data I was able to provide ?
Thanks,
Marketa

[Marketa01]

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])

)

RETURN

SWITCH(

    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"

)