Forum Discussion

ITSME123's avatar
ITSME123
New Member
3 years ago

Help please, sum 1 column if another column contains certain text but ignore slicers

Hi all,

 

I am looking for a little help, I have a table with a column named Value, I want to sum all of the value field, if another column "Data" column contains text "Delivered" but I need it to ignore any of the multiple slicers that are onscreen.

 

So in short... Just return the sum of the value field for anything that is delivered in the data column, without any slicer effecting the overall figure for this field.

 

Need Help

4 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    Try

    Total delivered =
CALCULATE (
    SUM ( 'Table'[Value] ),
    REMOVEFILTERS (),
    TREATAS ( { "Delivered" }, 'Table'[Data] )
)
    • ITSME123's avatar
      ITSME123
      New Member
      3 years ago
      • Hi, thanks for the reply, I have tried this and it gives a huge figure of 2.08 million, I am expecting to see around 32k. It looks like it is summing everything in the value column and not just the delivered 
      • johnt75's avatar
        johnt75
        Icon for Super User rankSuper User
        3 years ago

        Try running the below query in DAX Studio

        EVALUATE
ROW (
    "all rows", COUNTROWS ( 'Table' ),
    "filtered rows", CALCULATE ( COUNTROWS ( 'Table' ), TREATAS ( { "Delivered" }, 'Table'[Data] ) )
)

        That should give 2 different numbers.

    • ITSME123's avatar
      ITSME123
      New Member
      3 years ago

      Just awaiting the install of Dax studio then I will test, thank you