Forum Discussion
Combine Columns
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
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?
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?
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.
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