ALLSELECTED INSIDE OF ALLEXCEPT?

[Anonymous]

ALLSELECTED inside of ALLEXCEPT is not possible.

Is there a workaround, using INTERSECT of EXCEPT?

In the table below, the last column is fine. It shows the total of each 'NL' line.

But, I want this column to respond to the selected filters on the User, P&L, Department, and Job Grade columns.

So if I do this:

The amount shown on the Salary line should be: 11,458, and not still 91,875

My current measure for this column is this:

Total Amount [GBP] Selected Persons:=

Total Amount Selected Persons :=
VAR FilteredTable =
CALCULATETABLE (
Payroll_Table,
ALLSELECTED ( Payroll_Table[User ID] ),
ALLSELECTED ( Payroll_Table[P&L] ),
ALLSELECTED ( Payroll_Table[Department] ),
ALLSELECTED ( Payroll_Table[Job Grade] )
)
RETURN
CALCULATE (
SUM ( [Amount] ),
ALLEXCEPT ( Payroll_Table, Payroll_Table[Date], Payroll_Table[NL Line] )
)

(ignore the variable for now)

Any ideas on how to achieve this?

[Anonymous]

I also tried this, but it doesn't work:

CALCULATE (

SUM ( [Amount] ),

ALLEXCEPT ( Payroll_Table, Payroll_Table[Date], Payroll_Table[NL Line] ),

ALLSELECTED ( Payroll_Table[User ID] ),

ALLSELECTED ( Payroll_Table[P&L] ),

ALLSELECTED ( Payroll_Table[Department] ),

ALLSELECTED ( Payroll_Table[Job Grade] )

)

[amitchandak]

@Anonymous , try like

CALCULATE (
SUM ( [Amount] ),
filter (allselected( Payroll_Table), Payroll_Table[Date] =max(Payroll_Table[Date]) && Payroll_Table[NL Line] = max(Payroll_Table[NL Line]))
)

Payroll_Table is actual table not the car table you used in one formula

[Anonymous]

Your measure doesn't work I'm afraid.

I get this, which is quite wrong

[Anonymous · Accepted solution]

Ok, I think I resolved it.

Because of the Table structure of the Pivot Table, I had to include the First, and Last Name fields, as well:

Total Amount Selected Persons :=
CALCULATE (
SUM ( [Amount] ),
ALLSELECTED ( Payroll_Table[User ID] ),
ALLSELECTED ( Payroll_Table[First Name] ),
ALLSELECTED ( Payroll_Table[Last Name] ),
ALLS ( Payroll_Table[P&L] ),
ALL ( Payroll_Table[Department] ),
ALL ( Payroll_Table[Job Grade] )
)

Results are now better.