Forum Discussion

Newbie_1's avatar
Newbie_1
Frequent Visitor
6 years ago
Solved

Exclude rows from sum based on condition

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.

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    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

5 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    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's avatar
      Newbie_1
      Frequent Visitor
      6 years ago

      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's avatar
        v-lili6-msft
        Community Support
        6 years ago

        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

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    6 years ago

    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's avatar
      Newbie_1
      Frequent Visitor
      6 years ago

      Thanks a lot! This worked like a charm!