Calculate % of a Subset with Page Level Filters

[TimKroemer]

Hi All

I am trying to calculate % of a subset of data but respecting active Page Level Filters. Put differently, I have staff that are classified by category (e.g. A - Z) and function (e.g. Construction). 

I am trying to calculate the headcount in each category as a % of headcount in a particular function (e.g. Category A is x% of Construction headcount).

I have tried creating a new column using the following formula:

% of Construction = CALCULATE(COUNTROWS(Payroll),FILTER(Payroll,Payroll[Designation]=EARLIER(Payroll[Designation])))/
CALCULATE(COUNTROWS(Payroll),FILTER(Payroll,Payroll[Tier]="Construction"))

but this consistently calculates percentages on a total basis and does not consider active filters (for example, I might select a project filter to view only certain staff that have been allocated to the project - I then need Category A is x% of Construction headcount within Project y).

I have also tried using a measure calculation using the following formula (amongst others):

(M) % of Construction = COUNT(Payroll[Emp Number])/COUNTAX(ALLSELECTED(Payroll),Payroll[Tier]="Construction")

Any help would be greatly appreciated!

This only looks at the information shown in the table so always gives me 100%.

[Anonymous]

Hi @TimKroemer,

Slicer works on visual level, you can't use it to affect the result of calculate column. These formula of calculated column has been pretreatmented in dax formula and it can't get the chosen item from slicer.

In my opinion, I'd like to suggest you use measure and allselected filter to calculate the result based on the chosen of slicer.

In addition, you can also try to use power query with parameterized, it will affect the source table dynamically.

Refereence link:

Deep Dive into Query Parameters and Power BI Templates

Regards,

Xiaoxin Sheng

[TimKroemer]

Xiaoxin

Thank you very much for taking the time to respond - I will try the approach you suggested and then hopefully conclude this thread with a workable solution based on your advice.

Gratefully,

Tim