Forum Discussion

tjeffries's avatar
tjeffries
Helper I
6 years ago
Solved

Counting rows by date and ID

All

 

Need help if possible.   

I take a weekly snapshot of one of my tables and I need a way to count the total number orders created weekly so I can calculate percentage of orders created by each employee id per week

 

 Example below data is below .  I tried to create a measure with countrows for each employee id /total orders measure but total orders counted all 13000 rows and employee ID counted all the rows for each id

 

Report DateOrderemployee id
1-Jun60007777771
1-Jun600077777732
1-Jun60007777772
7-Jun60007777775
7-Jun60007777775
7-Jun60007777775
10-Jun60007777771
10-Jun60007777775
10-Jun600077777732
17-Jun60007777771
17-Jun60007777776

 

thank you

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi tjeffries 

    Assume you create a date table as below:

    date = ADDCOLUMNS(CALENDAR(DATE(2019,1,1),TODAY()),"year-week",YEAR([Date])&"-"&WEEKNUM([Date],2))

    create measures

    countall_week=calculate(count(table[order_id]),allexcept(date,date[year-week]))
countall_week_person=calculate(count(table[order_id]),allexcept(date,date[year-week]),values(table[employee_id]))
%=[countall_week_person]/countall_week

    Please feel free to ask us if you have any more problems.

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

2 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    tjeffries try this measure

     

    Base Measure = COUNTROWS ( Table )

% Measure = 
DIVIDE ( [Base Measure], CALCULATE ( {Base Measure], ALLSELECTED ( Table ) ) )

     

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi tjeffries 

    Assume you create a date table as below:

    date = ADDCOLUMNS(CALENDAR(DATE(2019,1,1),TODAY()),"year-week",YEAR([Date])&"-"&WEEKNUM([Date],2))

    create measures

    countall_week=calculate(count(table[order_id]),allexcept(date,date[year-week]))
countall_week_person=calculate(count(table[order_id]),allexcept(date,date[year-week]),values(table[employee_id]))
%=[countall_week_person]/countall_week

    Please feel free to ask us if you have any more problems.

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.