Forum Discussion

knightkblack's avatar
knightkblack
Frequent Visitor
8 years ago
Solved

Filtering with a Summed column

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 measur...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

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

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's avatar
knightkblack
Frequent Visitor
8 years ago

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

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

      Hi knightkblack,

       

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

       

      Regards,
      Frank