Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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.   Bu...
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

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's avatar
Anonymous
Not applicable
5 years ago

Your measure doesn't work I'm afraid.

 

I get this, which is quite wrong

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.