Forum Discussion

surfboarder1998's avatar
surfboarder1998
New Member
3 years ago
Solved

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.

Need Help
  • barritown's avatar
    barritown
    3 years ago

    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

6 Replies

  • barritown's avatar
    barritown
    Solution Sage
    3 years ago

    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's avatar
      surfboarder1998
      New Member
      3 years ago

      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's avatar
        barritown
        Solution Sage
        3 years ago

        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?