topic Re: Calculate selected values in a column to show percentage differences in DAX Commands and Tips

Calculate selected values in a column to show percentage differences

AMBP1973 — Tue, 11 Nov 2025 01:33:31 GMT

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.

Re: Calculate selected values in a column to show percentage differences

Hans-Georg_Puls — Tue, 11 Nov 2025 08:09:08 GMT

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?

Re: Calculate selected values in a column to show percentage differences

Zanqueta — Tue, 11 Nov 2025 15:47:42 GMT

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:

Re: Calculate selected values in a column to show percentage differences

AMBP1973 — Wed, 12 Nov 2025 02:10:20 GMT

Hello Hans-Georg_Puls , I am unsure how to go about changing the structure of the table, could you please assist?

Re: Calculate selected values in a column to show percentage differences

AMBP1973 — Wed, 12 Nov 2025 02:14:34 GMT

Thankyou Zanqueta , THis provided a parcial solution

Re: Calculate selected values in a column to show percentage differences

AMBP1973 — Wed, 12 Nov 2025 04:13:32 GMT

Hans-Georg_Puls

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

Re: Calculate selected values in a column to show percentage differences

Hans-Georg_Puls — Wed, 12 Nov 2025 07:14:19 GMT

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:

Create a duplicate or reference of the origin table
Unpivot the three role columns
Do some renaming
Create an index column
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.