Exclude rows from sum based on condition

[Newbie_1]

Hi everyone,

I am trying to achieve a measure that calculates the total of column 'value' but excludes certain rows based on multiple conditions because if I Calculate the total of the column, there will be some double counting. The double counting only occurs when the month is still open, and the columns through which this issue can be identified in are in Document type (if it's equal to SD)  and Purchase order Description (if it begins on an "@")
The original table is much larger with other rows and columns but I have just attached the higligths.
For starters, I have created a measure (Month(Today)) so that PowerBI always knows which month it is, but that's how far I got when trying to use the CALCULATE and IF DAXs.

My problem is that Calculate is not compatible with TRUE/FALSE statements, i.e., (Month(Today)) and IF statements cannot refer to a column, only a measure, as far as I undestand.

What I need is an automate formula than can calculate the total of the value column but exclude any row of the total IF you're in the current (open month) and Document type = SD or Purchase Order Description starts with "@"
Is this possible or do I have to perform multiple steps? i.e. adding a new column to isolate the "@" or any other step?

The inverse would also be sufficient, namely sum all the rows in the Value column if current month = Month Number AND Docuemnt type = "SD" / Product Description order starts with "@" and I could just deduct this value from the total.

[v-lili6-msft]

hi  @Newbie_1 

You try this measure

Measure =
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        Table,
        MONTH ( TODAY () ) = Table[Month Number]
            && Table[Document Type] = "SD"
            && LEFT ( Table[Purchase Order Description], 1 ) = "@"
    )
)

Regards,

Lin

[Newbie_1]

Thanks a lot! This worked like a charm!

[az38]

Hi @Newbie_1 

create a measure

Total Value Measure = 
CALCULATE(SUM('Table'[Value]), ALL(Table), MONTH(TODAY()) = Table[Month Number], Table[Document Type] = "SD", LEFT(Table[Purchase Order Description], 1) = "@")

[Newbie_1]

Thank you for the effort, unfortunately this generated the error I have been facing before, namely that I get an error because DAX does not allow the comparison of Values of Interger with Values of type text. Probably it's my dataset that prohibits it but the solution below help me to mediate it!

[v-lili6-msft · Accepted solution]

hi @Newbie_1 

Do you mean your Month Number column is text format,so just adjust the formula as below with VALUE Function

Measure =
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        Table,
        MONTH ( TODAY () ) = VALUE(Table[Month Number])
            && Table[Document Type] = "SD"
            && LEFT ( Table[Purchase Order Description], 1 ) = "@"
    )
)

Regards,

Lin