ALLEXCEPT with filter

[Anonymous]

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 Name	Year	Element Category	Organisation	Month	Date	Cost Total	Cost in 000
AAA	2023	Cash Out	IND	12	01/Dec/23	6.462.000 kr.	156.000, kr.
AAA	2023	Cash Out	IND	11	01/Nov/23	6.462.000 kr.	158.000, kr.
AAA	2023	Cash Out	IND	10	01/Oct/23	6.462.000 kr.	200.000, kr.
AAA	2023	Cash Out	IND	9	01/Sep/23	6.462.000 kr.	200.000, kr.
AAA	2023	Cash Out	IND	8	01/Aug/23	6.462.000 kr.	200.000, kr.
AAA	2023	Cash Out	IND	7	01/Jul/23	6.462.000 kr.	200.000, kr.
AAA	2023	Cash Out	IND	6	01/Jun/23	6.462.000 kr.	158.000, kr.
AAA	2023	Cash Out	IND	5	01/May/23	6.462.000 kr.	158.000, kr.
AAA	2023	Cash Out	IND	4	01/Apr/23	6.462.000 kr.	158.000, kr.
AAA	2023	Cash Out	IND	3	01/Mar/23	6.462.000 kr.	158.000, kr.
AAA	2023	Cash Out	IND	2	01/Feb/23	6.462.000 kr.	158.000, kr.
AAA	2023	Cash Out	IND	1	01/Jan/23	6.462.000 kr.	158.000, kr.
AAA	2023	Cash Out	IND	12	01/Dec/23	6.462.000 kr.	500.000, kr.
AAA	2023	Cash Out	IND	12	01/Dec/23	6.462.000 kr.	400.000, kr.
AAA	2023	Cash Out	IND	11	01/Nov/23	6.462.000 kr.	200.000, kr.
AAA	2023	Cash Out	IND	10	01/Oct/23	6.462.000 kr.	300.000, kr.
AAA	2023	Cash Out	IND	9	01/Sep/23	6.462.000 kr.	500.000, kr.
AAA	2023	Cash Out	IND	8	01/Aug/23	6.462.000 kr.	300.000, kr.
AAA	2023	Cash Out	IND	7	01/Jul/23	6.462.000 kr.	100.000, kr.
AAA	2023	Cash Out	IND	6	01/Jun/23	6.462.000 kr.	100.000, kr.
AAA	2023	Cash Out	IND	5	01/May/23	6.462.000 kr.	200.000, kr.
AAA	2023	Cash Out	IND	3	01/Mar/23	6.462.000 kr.	200.000, kr.
AAA	2023	Cash Out	COO	11	01/Nov/23	6.462.000 kr.	200.000, kr.
AAA	2023	Cash Out	COO	9	01/Sep/23	6.462.000 kr.	200.000, kr.
AAA	2023	Cash Out	COO	6	01/Jun/23	6.462.000 kr.	200.000, kr.
AAA	2023	Cash Out	COO	6	01/Jun/23	6.462.000 kr.	100.000, kr.
AAA	2023	Cash Out	COO	5	01/May/23	6.462.000 kr.	100.000, kr.
AAA	2023	Cash Out	COO	4	01/Apr/23	6.462.000 kr.	200.000, kr.
AAA	2023	Cash Out	COO	4	01/Apr/23	6.462.000 kr.	100.000, kr.
AAA	2023	Cash Out	COO	3	01/Mar/23	6.462.000 kr.	100.000, kr.
AAA	2023	Cash Out	COO	2	01/Feb/23	6.462.000 kr.	200.000, kr.
AAA	2023	Cash Out	COO	2	01/Feb/23	6.462.000 kr.	100.000, kr.
AAA	2023	Cash Out	COO	1	01/Jan/23	6.462.000 kr.	100.000, kr.

[Anonymous · Accepted solution]

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