Grouping Data from one column to create graph

[TristanO]

Hi All,

I am attempting to create a pie graph to show management how many hours are being billed out by each department but in order to do that I need to group together multiple job codes that are in the same column of a table.

Below is an example of part of the table -

Job Code	Hours
EL-2350	664
EL-2351	554
EL-2352	362
EL-2353	23
ME-1234	23
ME-1235	326
ME-1236	62
AT-7762	35
AT-7763	235
AT-7764	636
O-503	352
O-504	63
O-505	363

Basically what I want to do is set a filter that will group AT, EL, ME and O into their own groups and add the hours then create a pie graph that has the 4 created groups.

Because this data source is constantly updated I need the filter in place because new job codes are always being added.

Is there a way to do this from within Power BI Desktop?

Thanks in advance.

[Ashish_Mathur]

Hi,

You may also split data in the Query Editor window by using - as a delimiter.

[Anonymous · Accepted solution]

@TristanO,

Create a column using DAX below in your  table.
GroupName = LEFT(Table[Job Code],FIND("-",Table[Job Code])-1)

Then create a pie chart as follows. When new job codes are added, just click "Refresh" button in Power BI Desktop to bring the new data.


Regards,
Lydia