Calculated column with one filter selected and no filter

[tarekeal]

Hi, I have a table with date, salepersons, their location and their sales amount and a status column saying if they are internal or external.

Date	Salespersons	Location	Amount	Status
12/1/2022	Jean	Brussels	125	Internal
12/1/2022	Alex	Brussels	150	External
13/1/2022	Bruno	Paris	130	Internal

In a calculated table using SUMMARIZECOLUMNS I'm summarizing the sales by location and date but I'm struggling with the status since I would like to differentiate between internal and ALL (which is basically the sum of both), not between internal and external, such as hereunder.

Date	Location	Status	Amount
12/1/2022	Brussels	Internal	125
12/1/2022	Brussels	All	275
13/1/2022	Paris	Internal	130

[wdx223_Daniel]

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

NewTable = 
VAR tablefilter =
SELECTCOLUMNS(
    FILTER (
        ADDCOLUMNS (
            Data,
            "@filter", IF ( Data[Status] = "Internal", "Internal", "All" )
        ),
        [@filter] = "Internal"
    ), "Date",  Data[Date], "Location", Data[Location], "@Status", [@filter], "@Amount", Data[Amount] )
VAR summarytable =

    SUMMARIZECOLUMNS (
        Data[Date],
        Data[Location],
        "@Status", IF("External" in VALUES(Data[Status]),"ALL", BLANK()),
        "@Amount", IF("External" in VALUES(Data[Status]), SUM ( Data[Amount] ), BLANK() )
    )
RETURN
UNION( tablefilter,
 summarytable )

[tarekeal]

Thanks a lot, I ended up using SUMMARIZECOLUMNS twice since the first table was not giving the right values with the real data.

[amitchandak]

@tarekeal why there is no all by pairs?

One way is

Union(

Summarize(Table, Table[Date], Table[Location], Table[Status], "Amount", Sum(Table[Amount]) ) ,

Summarize(Table, Table[Date], Table[Location], Status, "All", "Amount", Sum(Table[Amount]) )

)

or

Union(

Summarize(Table, Table[Date], Table[Location], Table[Status], "Amount", Sum(Table[Amount]), "_cnt", countrows(Table) ) ,

filter( Summarize(Table, Table[Date], Table[Location], Status, "All", "Amount", Sum(Table[Amount]), "_cnt", countrows(Table) ), "_cnt" >1)

)