Combine Columns

[surfboarder1998]

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.

[barritown]

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

My YouTube vlog in English

My YouTube vlog in Russian

[surfboarder1998]

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]

@surfboarder1998,

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?

[surfboarder1998]

I plan to slice by Work Status and use the Segment in the legend. Right now, the way that you have shown them would be great. I would be able to total the two allocation columns and and that total would still be for a specific Segment and have a specific Work Status.

I hope this makes sense. Let me know if there's more clarification I can provide and thank you very much for your help.

[barritown · Accepted solution]

@surfboarder1998, 

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

My YouTube vlog in English

My YouTube vlog in Russian

[surfboarder1998]

This is exactly the solution I was looking for. Thank you so much for your help barritown.