topic Re: Sum of column where other column = "Other" in DAX Commands and Tips

Sum of column where other column = "Other"

LewisFoulger — Wed, 09 Mar 2022 09:11:44 GMT

Hello,

I am new to DAX and am looking to create a measure that shows the sum of the weight which is not being recycled.

I believe this is how its done below but nothing is returning, calculate the sum but filter out "Other" (which is what is not being recycled).

Here is what I thought:

Non-Recyclable Waste = CALCULATE(SUM('Sims Waste Certificates'[Waste Weight (Ton)]),FILTER('Sims Waste Certificates',[Waste Type] = "Other"))

All help is appreciated! Thanks!

Re: Sum of column where other column = "Other"

Samarth_18 — Wed, 09 Mar 2022 09:20:42 GMT

Hi LewisFoulger ,

The thing which you put in filter condition is case sensitive so please make sure you have write "Other" correctly(with proper case) and you can try below code:-

Non-Recyclable Waste = CALCULATE(SUM('Sims Waste Certificates'[Waste Weight (Ton)]),'Sims Waste Certificates'[Waste Type] = "Other")

Non-Recyclable Waste = CALCULATE(SUM('Sims Waste Certificates'[Waste Weight (Ton)]),FILTER(all('Sims Waste Certificates'[Waste Type]),Sims Waste Certificates[Waste Type] = "Other"))

Thanks,

Samarth

Re: Sum of column where other column = "Other"

LewisFoulger — Wed, 09 Mar 2022 09:24:21 GMT

Hi Samarth_18,

I tried both and it returns "1". What I am looking for is to return the total weight of every item labelled "Other", maybe I am doing something completely different?

Re: Sum of column where other column = "Other"

LewisFoulger — Wed, 09 Mar 2022 09:26:43 GMT

I am looking to pull the total waste weight ton (ton) where waste type = Other

Re: Sum of column where other column = "Other"

Samarth_18 — Wed, 09 Mar 2022 09:32:30 GMT

LewisFoulger Based on datasnapshot you shared. Can you convert your measure to decimal number like below. Since your data for other is in few pointers only.