Forum Discussion
Combine Columns
I currently have a report that lists a primary and support person in two separate columns. It also lists the percent allocation for the primary person and a percent allocation for the support person in separate columns. Since anyone on the team can be support and lead on separate projects simultaneously I would like to show total that each person is utilized. I would have to add each of their total Lead and Support allocations in a stacked bar chart.
Example:
Thank you for any help that you can give and I look forward to hearing back from the forum.
Then I'd flatten the initial table with the code below, rename manually "Lead Name" into just "Name" (it can be also done in [DAX] via SELECTCOLUMNS) and use this flattened table in visuals.
In plain text:
Table = UNION ( SUMMARIZE ( Data, [Work Status], [Segment], [Lead Name], "Role", "Lead", "Allocation", SUM ( Data[Lead Allocation] ) ), FILTER ( SUMMARIZE ( Data, [Work Status], [Segment], [Support Name], "Role", "Support", "Allocation", SUM ( Data[Support Allocation] ) ), NOT ISBLANK ( [Support Name] ) ) )Best Regards,
Alexander
6 Replies
- barritown
Solution Sage
Hi surfboarder1998,
One of the options is to reorganize your table into a summary like the one below, then you can easily create a bar chart using its columns.
In plain text for convenience:
People = ADDCOLUMNS ( SELECTCOLUMNS ( FILTER ( DISTINCT ( UNION ( VALUES ( Data[Lead Name] ), VALUES ( Data[Support Name] ) ) ), ISBLANK( [Lead Name] ) = FALSE() ), "Name", [Lead Name] ), "Lead", VAR CurrentName = [Name] RETURN SUMX ( FILTER ( Data, [Lead Name] = CurrentName), [Lead Allocation] ), "Support", VAR CurrentName = [Name] RETURN SUMX ( FILTER ( Data, [Support Name] = CurrentName), [Support Allocation] ) )Best Regards,
Alexander
Thanks very much barritown. This solution works well. I was wondering if it would also work if I needed to have two additional fields added to it, SEGMENTATION and WORK STATUS. Both are text fields. I have added them to an example below.
Could the code above be adjusted to add this in to the summary table?
- barritown
Could you please clarify how you see the final result with these additional columns? Do you plan any additional slicing by Work Status or Segment?
