CODEWITHSUNDEEP
powerbidax
Bharath
Bharath

Reputation: 416

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

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. table

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.

actual image

Upvotes: 1

Views: 2585

Answers (1)

mkRabbani
mkRabbani

Reputation: 16908

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])

Upvotes: 3

Related Questions