Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

ALLEXCEPT with filter

Hi everyone,

 

I am calculating [Cost Total] that takes all values in [Cost in 000]. Below formula works but I want to also include [Organization] = "IND" and "COO"

 

Cost Total = CALCULATE(SUM('Details Actuals+FC (Selection)'[Cost in 000]), ALLEXCEPT('Details Actuals+FC (Selection)', 'Details Actuals+FC (Selection)'[Project Name], 'Details Actuals+FC (Selection)'[Year], 'Details Actuals+FC (Selection)'[Element Category]))

 

Project NameYearElement CategoryOrganisationMonthDateCost TotalCost in 000
AAA2023Cash OutIND1201/Dec/236.462.000 kr.156.000, kr.
AAA2023Cash OutIND1101/Nov/236.462.000 kr.158.000, kr.
AAA2023Cash OutIND1001/Oct/236.462.000 kr.200.000, kr.
AAA2023Cash OutIND901/Sep/236.462.000 kr.200.000, kr.
AAA2023Cash OutIND801/Aug/236.462.000 kr.200.000, kr.
AAA2023Cash OutIND701/Jul/236.462.000 kr.200.000, kr.
AAA2023Cash OutIND601/Jun/236.462.000 kr.158.000, kr.
AAA2023Cash OutIND501/May/236.462.000 kr.158.000, kr.
AAA2023Cash OutIND401/Apr/236.462.000 kr.158.000, kr.
AAA2023Cash OutIND301/Mar/236.462.000 kr.158.000, kr.
AAA2023Cash OutIND201/Feb/236.462.000 kr.158.000, kr.
AAA2023Cash OutIND101/Jan/236.462.000 kr.158.000, kr.
AAA2023Cash OutIND1201/Dec/236.462.000 kr.500.000, kr.
AAA2023Cash OutIND1201/Dec/236.462.000 kr.400.000, kr.
AAA2023Cash OutIND1101/Nov/236.462.000 kr.200.000, kr.
AAA2023Cash OutIND1001/Oct/236.462.000 kr.300.000, kr.
AAA2023Cash OutIND901/Sep/236.462.000 kr.500.000, kr.
AAA2023Cash OutIND801/Aug/236.462.000 kr.300.000, kr.
AAA2023Cash OutIND701/Jul/236.462.000 kr.100.000, kr.
AAA2023Cash OutIND601/Jun/236.462.000 kr.100.000, kr.
AAA2023Cash OutIND501/May/236.462.000 kr.200.000, kr.
AAA2023Cash OutIND301/Mar/236.462.000 kr.200.000, kr.
AAA2023Cash OutCOO1101/Nov/236.462.000 kr.200.000, kr.
AAA2023Cash OutCOO901/Sep/236.462.000 kr.200.000, kr.
AAA2023Cash OutCOO601/Jun/236.462.000 kr.200.000, kr.
AAA2023Cash OutCOO601/Jun/236.462.000 kr.100.000, kr.
AAA2023Cash OutCOO501/May/236.462.000 kr.100.000, kr.
AAA2023Cash OutCOO401/Apr/236.462.000 kr.200.000, kr.
AAA2023Cash OutCOO401/Apr/236.462.000 kr.100.000, kr.
AAA2023Cash OutCOO301/Mar/236.462.000 kr.100.000, kr.
AAA2023Cash OutCOO201/Feb/236.462.000 kr.200.000, kr.
AAA2023Cash OutCOO201/Feb/236.462.000 kr.100.000, kr.
AAA2023Cash OutCOO101/Jan/236.462.000 kr.100.000, kr.
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous,

    You can try to use the following measure formula if it suitable for your requirement:

    Cost Total =
CALCULATE (
    SUM ( 'Details Actuals+FC (Selection)'[Cost in 000] ),
    FILTER (
        ALLSELECTED ( 'Details Actuals+FC (Selection)' ),
        'Details Actuals+FC (Selection)'[Organization] IN { "IND", "COO" }
    ),
    VALUES ( 'Details Actuals+FC (Selection)'[Project Name] ),
    VALUES ( 'Details Actuals+FC (Selection)'[Year] ),
    VALUES ( 'Details Actuals+FC (Selection)'[Element Category] )
)

    Regards,

    Xiaoxin sheng

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous,

    You can try to use the following measure formula if it suitable for your requirement:

    Cost Total =
CALCULATE (
    SUM ( 'Details Actuals+FC (Selection)'[Cost in 000] ),
    FILTER (
        ALLSELECTED ( 'Details Actuals+FC (Selection)' ),
        'Details Actuals+FC (Selection)'[Organization] IN { "IND", "COO" }
    ),
    VALUES ( 'Details Actuals+FC (Selection)'[Project Name] ),
    VALUES ( 'Details Actuals+FC (Selection)'[Year] ),
    VALUES ( 'Details Actuals+FC (Selection)'[Element Category] )
)

    Regards,

    Xiaoxin sheng