Calculating percentage of subtotals with dynamic filtering

[korina]

Hello,

I am trying to create a line chart like the one below, calculating the percentage of each gender within each category, rather than that of the total population:

 

 

I have used two formulas to achieve that.

First, I created a new column:

Total by Category = CALCULATE(COUNT(Headcount[Pers. No.]), ALLEXCEPT(Headcount, Headcount[Categories]))

And then, based on that, a measure:

Employees% = Headcount[Employees] / MAX(Headcount[Total by Category])

The problem starts when I use one of the filters at the top of the page (Division, Subdivision, Org. Unit).

I try to work around this by including them in the "Total by Category" calculation, like this:

Total by Category = CALCULATE(COUNT(Headcount[Pers. No.]), ALLEXCEPT(Headcount, Headcount[Categories], Headcount[Division], Headcount[Subdivision], Headcount[Org. Unit]))

However, this fixes the problem for the deepest level (Org. Unit), and when I filter by subdivision I get really weird percentages (the sums of the org. units of that subdivision).

Can anyone help me fix this?

Thank you very much,

Korina

[Anonymous]

@korina 

You would need to use Allselected instead of ALLEXCEPT. Try the following measure:

Total by Category = CALCULATE(COUNT(Headcount[Pers. No.]), Filter(Allselected(Headcount),[Categories]=MAX([Categories]), [Division]=MAX([Division]), [Subdivision]=MAX([Subdivision]), [Org. Unit]=MAX([Org. Unit])))

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[korina · Accepted solution]

Hello @Anonymous! Thank you for your help.

Unfortunately, DAX syntax doesn't allow for more than 2 arguments to be passed to the Filter function, so it doesn't work.

However, I posted this problem again, and somebody found a solution, please see below:

https://community.powerbi.com/t5/Desktop/100-Stacked-Line-Chart-responding-to-slicers-filters/m-p/15...

[amitchandak]

@korina , Try a measure like this and check 

Total by Category = CALCULATE(COUNT(Headcount[Pers. No.]), Filter(allselected(Headcount), Headcount[Categories] = all(Headcount[Categories])))

[korina]

Thank you @amitchandak 

I have tried this, and I get the following error:

Any ideas?