Measure for Monthly Sum with Filter

[vkisa]

Hi,

I need some help to get measure that calculates the total deposit for specific customers that had accomplished sales both product A and B (green cells).

I don't want to create a new calculated monthly table (Temporary Table).

I want to get TotalDeposit(MultipleSales) column in my result table.

Data Table-Table1

Year	Month	Customerid	SalesAmountA	SalesAmountB	Deposit
2024	1	1000	50	0	10
2024	1	1000	30	20	10
2024	1	1000	10	10	15
2024	1	1000	0	0	0
2024	1	1000	20	20	0
2024	1	1001	0	10	5
2024	1	1001	0	0	5
2024	1	1001	0	0	0
2024	1	1001	0	0	10
2024	1	1002	5	0	0
2024	1	1002	5	0	20
2024	1	1002	5	10	10

Temporary Table (I don't want to create this table, it is just to explain what I want to)

CustomerID	Year	Month	MonthlySalesA	MonthlySalesB	MonthlyDeposit
1000	2024	1	110	50	35
1001	2024	1	0	10	20
1002	2024	1	15	10	30

Table2(my result table)

Year	Month	CountofCustomer(MultipleSales)	TotalDeposit	TotalDeposit(MultipleSales)
2024	1	2	85	"35+30=65"

Thank you,

Veli

[Anonymous · Accepted solution]

Hi @vkisa ,

Regarding your question, Is it because the sales volume is 0 that excludes the calculation of '1001'?

Try this.

MEASURE =
VAR _table =
    ADDCOLUMNS (
        SUMMARIZE (
            'Table1',
            [Customerid],
            "SalesA", SUM ( Table1[SalesAmountA] ),
            "SalesB", SUM ( Table1[SalesAmountB] ),
            "Deposit", SUM ( Table1[Deposit] )
        ),
        "isAccomplish",
            IF ( [SalesA] <> 0 && [SalesB] <> 0, TRUE (), FALSE () )
    )
RETURN
    SUMX ( FILTER ( _table, [isAccomplish] = TRUE () ), [Deposit] )

Best Regards,
Wenbin Zhou

[vkisa]

Thank you @Anonymous , it worked !