Filtering with a Summed column

[knightkblack]

Hi all,

I've got a column that says how many miles a truck has driven for a certain order. In a given week a truck may run anywhere from 2 to 6 orders. What I'm trying to do is create a measure or a filter that will return a count of how many trucks ran at least 2200 miles. I'm unsure how to do this though, because I have to create the filter on the summed total for that week for a particular truck number. 

As you can see in the image, there are two different kinds of miles tracked for the truck. I've got a measure called Miles per Truck that sums those two columns together for a total miles ran. My filter though wants to look at the total miles for the week not each order. For example form the image, Truck 522629 ran 2428 Miles and Truck 522630 ran 1918 miles. So 522629 would be included in the count, 522630 would not be included.

[v-frfei-msft]

Hi @knightkblack,

Based on my test, we can take the following steps to meet your requirement.

1. Enter the data and create a calculated column.

Column = CALCULATE(SUM(Table1[LD Miles])+SUM(Table1[E Miles]),FILTER(ALL(Table1),Table1[Truck#]=EARLIER(Table1[Truck#])))

2. Create a measure as below.

countdis = CALCULATE(DISTINCTCOUNT(Table1[Truck#]),FILTER(Table1,Table1[Column]>2000))

3. Then we can get the count of Truck that the total miles is above 2000.

For more details, please check the pbix as attached.

https://www.dropbox.com/s/hqepgid4v5edlup/Filtering%20with%20a%20Summed%20column.pbix?dl=0

Regards,

Frank

[knightkblack]

Hi @v-frfei-msft

Thanks for that. It seems to be working, although there is one issue. 

It appears to be calculating the total miles for the lifetime of the truck. I have a filter applied to my report that only shows the trucks and the loads they carried for last week. This filter doesn't seem to be affecting the column that you provided. So the measure is just returning the total number of trucks that I have for last week because all the trucks have run over 2000 since they've been in service. It should be returning only the trucks that ran over 2000 miles last week. 

Any thoughts as to why that might be happening? Is there something else that needs to be done?

[v-frfei-msft · Accepted solution]

Hi @knightkblack,

I made same update to my sample data by adding a column of date, and you can refer to the following steps to get the result as we excepetd..

1. Create a calculated column using the formula.

milelastweek1 = CALCULATE(SUM(Table1[E Miles])+SUM(Table1[LD Miles]),FILTER(ALL(Table1),Table1[date]<=TODAY() && Table1[date]>=TODAY()-7 && Table1[Truck#]= EARLIER(Table1[Truck#])))

2. Create a measure to get the count of Truck that meet the requirement.

countdis = CALCULATE(DISTINCTCOUNT(Table1[Truck#]),FILTER(Table1,Table1[milelastweek1]>2000))

For more details, please check the pbix as attached. If it doesn't meet your requirement, kindly share your pbix to me.

https://www.dropbox.com/s/hqepgid4v5edlup/Filtering%20with%20a%20Summed%20column.pbix?dl=0

Regards,

Frank

[v-frfei-msft]

Hi @knightkblack,

Does that make sense? If so, kindly mark my answer as a solution to close the case.

Regards,
Frank