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...
Need Help
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