Calculate selected values in a column to show percentage differences

[AMBP1973]

Hello, I have 3 columns that all indictae certain positions against a task, each position may assume 1 or all roles for that task, some roles also being 'unassigned' (Content Author, Content Manager, Approver). I have already aggregated these into a single column 

Content Manage. Roles Aggr = 'DI Review'[Content Author] & "-" & 'DI Review'[Content Manager] & "-" & 'DI Review'[Approver]

I am now trying to acheive the following:

1. Create a pie visual that shows % of assigned vs unassigned. For example total number of positions (i.e position titles) vs total number of 'unassigned'

2. Create a slicer that allows selection of a single position, as currently, due the position values being repeated across 3 columns it is showing multiple values in each slection.

Unfortuantely I cannot share that data (please refer to screen shots for context). I would appreciate any assistance. Please also note that I cannot open the latest PBIX files, so dax and instructions would be much appreciated.

[Zanqueta · Accepted solution]

Hi @AMBP1973.

Lets my suggestions for goal 1:

First Step: Create a calculated table that unpivots the 3 columns into a single column of positions

PositionsExpanded = 
UNION(
    SELECTCOLUMNS(Task, "Position", Task[Content Author]),
    SELECTCOLUMNS(Task, "Position", Task[Content Manager]),
    SELECTCOLUMNS(Task, "Position", Task[Aprover])
)

Then, create a calculated column to classify each row:

RoleStatus =
IF(
    PositionsExpanded[Position] = "unassigned",
    "Unassigned",
    "Assigned"
)

Now, create a measure to count and calculate percentages:

COUNTROWS(PositionsExpanded)

include RoleStatus in Legend:

[Zanqueta · Accepted solution]

Hi @AMBP1973.

Lets my suggestions for goal 1:

First Step: Create a calculated table that unpivots the 3 columns into a single column of positions

PositionsExpanded = 
UNION(
    SELECTCOLUMNS(Task, "Position", Task[Content Author]),
    SELECTCOLUMNS(Task, "Position", Task[Content Manager]),
    SELECTCOLUMNS(Task, "Position", Task[Aprover])
)

Then, create a calculated column to classify each row:

RoleStatus =
IF(
    PositionsExpanded[Position] = "unassigned",
    "Unassigned",
    "Assigned"
)

Now, create a measure to count and calculate percentages:

COUNTROWS(PositionsExpanded)

include RoleStatus in Legend:

[AMBP1973]

Thankyou @Zanqueta , THis provided a parcial solution

[DNMAF]

Hi @AMBP1973 ,

independently of what you wanna do with the data I would recommend to change the structure of your table to something like this:

Assignement ID	Task	Role	Assigned
1	Task 1	Content Author	Unassigned
2	Task 1	Content Manager	Unassigned
3	Task 1	Approver	Unassigned
4	Task 2	Content Author	Unassigned
5	Task 2	Content Manager	Unassigned
6	Task 2	Approver	Unassigned
7	Task 3	Content Author	Technical Logistics Officer
8	Task 3	Content Manager	Technical Logistics Officer
9	Task 3	Approver	Deputy Director Supportability Analysis
10	Task 4	Content Author	Technical Logistics Officer
11	Task 4	Content Manager	Technical Logistics Officer
12	Task 4	Approver	Deputy Director Supportability Analysis

You should do that restructuring with Power Query. 

Based on this structure calculations and slicing should be much easier.

Does that help you? Do you need any further assistance?

[AMBP1973]

Hello @DNMAF , I am unsure how to go about changing the structure of the table, could you please assist?

[AMBP1973]

@DNMAF 

Could you please provide additional guidance on restructuring to acheive the below. Many thanks

Assignement ID	Task	Role	Assigned
1	Task 1	Content Author	Unassigned
2	Task 1	Content Manager	Unassigned
3	Task 1	Approver	Unassigned
4	Task 2	Content Author	Unassigned
5	Task 2	Content Manager	Unassigned
6	Task 2	Approver	Unassigned
7	Task 3	Content Author	Technical Logistics Officer
8	Task 3	Content Manager	Technical Logistics Officer
9	Task 3	Approver	Deputy Director Supportability Analysis
10	Task 4	Content Author	Technical Logistics Officer
11	Task 4	Content Manager	Technical Logistics Officer
12	Task 4	Approver	Deputy Director Supportability Analysis

[DNMAF · Accepted solution]

Hi @AMBP1973 ,

with pleasure.

Assuming that this is the structure of your origin or source table:

and assuming that you have this table already loaded into Power Query,

you have to perform the following steps to transform the table to the format I suggested:

1. Create a duplicate or reference of the origin table
2. Unpivot the three role columns
3. Do some renaming
4. Create an index column
5. Reorder the columns if you want to

You can add these steps without any coding using the UI or copy the following Power Query (M) Code:

let
  Source = OriginTable,
  #"Unpivoted Only Selected Columns" = Table.Unpivot(Source, {"Content Author", "Content  Manager", "Approver"}, "Attribute", "Value"),
  #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Only Selected Columns",{{"Attribute", "Role"}, {"Value", "Assigned"}}),
  #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 1, 1, Int64.Type),
  #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Task", "Role", "Assigned"})
in
  #"Reordered Columns"

Hope that helps and works for you.