Counting rows by date and ID

[tjeffries]

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 Date	Order	employee id
1-Jun	6000777777	1
1-Jun	6000777777	32
1-Jun	6000777777	2
7-Jun	6000777777	5
7-Jun	6000777777	5
7-Jun	6000777777	5
10-Jun	6000777777	1
10-Jun	6000777777	5
10-Jun	6000777777	32
17-Jun	6000777777	1
17-Jun	6000777777	6

thank you

[v-juanli-msft · Accepted solution]

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.

[parry2k]

@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.⚡