Forum Discussion

Acarbalacar's avatar
Acarbalacar
Frequent Visitor
8 years ago
Solved

ALLEXCEPT in a multitable setup

I have the current formula, to create a new column ranking stations according to their sum of committed cost with the desired filters in ALLEXCEPT.   Rank cost = RANKX( ALLSELECTED('Task List'[St...
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    8 years ago

    HI Acarbalacar,

     

    Nope, current allexcept function seems not support calculate across multiple tables.
    In my opinion, I'd like to suggest you use multiple allexcet function with different tables and other filters.

    Rank cost =
RANKX (
    ALLSELECTED ( 'Task List'[Station name] );
    CALCULATE (
        SUM ( 'Task List'[Committed Cost] );
        ALLEXCEPT ( 'Task List'; 'Task List'[Station name] );
        ALLEXCEPT ( 'table1'; 'table1'[Asset class] );
        ALLEXCEPT ( 'table2'; 'table2'[Cost Code name] );
        ALLEXCEPT ( 'table3'; 'Table3'[Logged Year-mon] );
        ALLEXCEPT ( 'table4'; 'table4'[Station type] );
        ALLEXCEPT ( 'table5'; 'table5'[Status]; 'table5'[Business Unit] );
        ALLEXCEPT ( 'table6'; 'table6'[Station number] )
    )
)

     

    Regards,

    Xiaoxin Sheng