Order count - Benchmark against Employee count

[DriesD]

Hello everyone,

First post here on the forum, relatively new to PowerBi, getting out of the basics and navigation and now struggling with some more 'complex' calculations where I cannot seem to find an answer in the many youtube videos or existing posts here. 

If anyone would be able to help with this, that would be great

I can easily count the total of orders per company code, in my easy date example below US1 - 3 and CA1 also 3.

next to this total I want to divide that total number by the amount of technicians per company code. this amount I have manually put in a separate table, put I cannot seem to do this with a DIVIDE or CALCULATE function...

(as dividing by the amount of technicians basically benchmarks the countries better)

Company Code	Ordernumber	Created On
US1	1001	1/5/2020
US1	1002	1/13/2020
US1	1003	1/7/2020
CA1	1004	1/8/2020
CA1	1005	1/10/2020
CA1	1006	1/10/2020

Second table

Company Code	Technicians
US1	10
CA1	5

Further down in my report I want to do the same thing, where I'm not looking at totals, but I'm looking at orders created per day, totals. Also this graph I would like to bring down to a level of orders created per day, divided by the amount of technicians.

[DriesD]

oh wow, such great and quick responses, thank you all! 

I will check the answers and provide feedback throughout the day/tomorrow!

[v-juanli-msft · Accepted solution]

Hi @DriesD 

With relationships and measure below, we could get the result easily.

total = DISTINCTCOUNT(Table1[Ordernumber])

average = [total]/SUM(Table2[Technicians])

But for the next question, total per day/ technicians, i don't know what technicians should be used.

Could you give me an example?

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.

[DriesD]

hi Maggie, it's basically the same. 
I'm trying to look at all orders created per company code, which you have shown in your example. Per day is the same thing, the total amount of orders created per day, per technician 

(so total order count created on that day, divided by total amount of technicians)
thanks for your reply, I will look into this and revert back!

[v-juanli-msft]

Hi @DriesD 

As i checked, my reply covers the above you concern.

If there is still any problem, please tell me.

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.

[sanimesa]

@DriesD  You need to create a relationship between the two tables on the Company Code column.

Then, create a calculated column in your Order table as follows:

Technicians = RELATED(CompanyCodesTable[Technicians])

Then create your measure that will provide you the orders per technician for each company.

Orders per Technician = DIVIDE(DISTINCTCOUNT(OrderTable[Ordernumber]), AVERAGE(OrderTable[Technicians]))

This measure will work if you drop it into a table where you put the conpany code from the company codes table.

A picture below shows how it will look. For the date, you will need to create a calendar column first and go from there.