Calculate sum of a column in power BI depending on a condition that should be evaluated over each row

Question

I have a table which looks like this: I need to calculate sum of all numbers in column :ADD , IF the number in column CHANGE is 0 AND column DELETE is 0.

The actual data looks like below. Its same as above. The items with red backets should be filtered out because there are values against delete and change for each unique number in MEMO column.

What is your expected output now with this new sample data? If there are Change/Delete for any MEMO, you wants to exclude all MEMO from the count? — mkRabbani
I want sum of Total Count for each unique TSframe_ Memo where , Change OR delete is zero . So sum of total count for each unique TSframe , when only ADD exists and there is no change or delete for that memo number. — Bharath
For example in above image i would want sum of Total_Count ignoring the memo numbers in red brackets. — Bharath
for example 18779 has 1 ADD and 1 CHANGE. You wants to exclude all rows for 18779? As there are at least 1 DELETE/CHANGE. Right? — mkRabbani

mkRabbani mkRabbani · Accepted Answer · 2020-09-30T07:14:12

Create a Measure as below to Exclude records from the list-

exclude = 

VAR current_row_tsframe_account = MIN(your_table_name[TSframe_Account])
VAR current_row_tsframe_memo = MIN(your_table_name[TSframe_Memo])

VAR find_delete_change_for_memo = 
CALCULATE(
    COUNT(your_table_name[TSframe_Memo]),
    FILTER(
        ALL(your_table_name),
        your_table_name[TSframe_Memo] = current_row_tsframe_memo
            && your_table_name[TSframe_Account] = current_row_tsframe_account
            && your_table_name[TSframe_Mode] IN {"CHANGE","DELETE"}
    )
)


RETURN 
IF(
    find_delete_change_for_memo > 0,
    "Yes",
    "No"
)

The above Measure will return Yes/No per row. You can now Apply visual/page level filter so that records only show where measure Exclude = No. Now this below measure will show your expected value-

total = SUM(your_table_name[TSframe_Memo])

Calculate sum of a column in power BI depending on a condition that should be evaluated over each row

1 Answers