Forum Discussion
ALLSELECTED INSIDE OF ALLEXCEPT?
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?
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.
4 Replies
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
