exclude some names from calculate

[Anonymous]

Hi guys,

I've got the number of wagons in one column and in another one ive got different client names. Literally just two columns. Now I need to sum wagon numbers only for two clients under title names "A" and "B". Then I need to sum wagon numbers again, but exclude those two clients. I cant figure out how to do it, when i have the names of those clients. 

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create two measures as below.

AB = 
CALCULATE(
    SUM('Table'[Num of wagons]),
    FILTER(
        ALL('Table'),
        [Client Name] in {"A","B"}
    )
)

Not AB = 
CALCULATE(
    SUM('Table'[Num of wagons]),
    FILTER(
        ALL('Table'),
        NOT([Client Name] in {"A","B"})
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

First off, you should not limit the measures to only 2 clients. They should work with any number of clients which would be selected from a slicer. Based on this assumption, you could write them like so:

[Wagons for Selected Clients] =
SUM( YourTable[Wagons] )

[Wagons for All excl. Selected] =
var __allClients =
    CALCULATE(
        [Wagons for Selected Clients],
        ALL( YourTable )
    )
var __selectedClients =
    [Wagons for Selected Clients]
return
    __allClients - __selectedClients

[amitchandak]

@Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.