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
    8 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,

 

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,

 

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

 

Regards,
Frank