Forum Discussion

Mahmoud_nabir's avatar
Mahmoud_nabir
Frequent Visitor
7 years ago

calculate sum with filter between two column

i want to Column Value = (count Number of Rows in Customers Visits  where Customer VisitDay = Planned Visit[VisitDay])

 

filter is only between column and single value but i want to sum filtring by 2 column.

 

 

 

Planned visitCustomer Visits

Need Help

4 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Mahmoud_nabir

    I don't understand what's the meaning of this sentence

    "filter is only between column and single value but i want to sum filtring by 2 column"

    could you tell me more about this and why you higlight the two columns in the two tables?

     

    Best regards

    Maggie

  • Mahmoud_nabir's avatar
    Mahmoud_nabir
    Frequent Visitor
    7 years ago

    i have 2 Table:-

    Planned Visits Details(view customer with planned visit day )

    planned Visit Week (total planned visit by week day) (Visitday Column is Text )

     

    i want to count the number of visits by week day

     

     

    Planned Visit Week 

    Visit Day            Planned_visit

    Friday                  = count (customerId) where planned Visit Week[VisitDay]= planned visit Week[Visitday]

    Saturday             = count (customerId) where planned Visit Week[VisitDay]= planned visit Week[Visitday]

     

    Planned Visits DetailsPlanned Visits Details

     

    Planned Visit Week

    • v-juanli-msft's avatar
      v-juanli-msft
      Icon for Community Support rankCommunity Support
      7 years ago

      Hi Mahmoud_nabir

      Does your "Planned Visit Week" table only have these two column?

      If it is, you could create a new "Planned Visit Week" table with "Planned Visits Details" table.

      Create a new table

      Table = SUMMARIZE(ALL(Table1),Table1[visitday],"count",CALCULATE(COUNT(Table1[id]),ALLEXCEPT(Table1,Table1[visitday])))

       

      Best Regards

      Maggie

    • v-juanli-msft's avatar
      v-juanli-msft
      Icon for Community Support rankCommunity Support
      7 years ago

      Hi Mahmoud_nabir

       

      If you have 0 row for one spefic weekday, for example FRIDAY 0, you could create a new table with a single column including seven weekdays from MONDAY to SUNDAY, then add another column to this weekday column.

      new table2 = ADDCOLUMN(new table,"count",CALCULATE(COUNT(Table1[id]),ALLEXCEPT(Table1,Table1[visitday])))

       

      Best Regards

      Maggie