cancel
Showing results for
Did you mean:
Regular Visitor

## Order count - Benchmark against Employee count

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.

1 ACCEPTED SOLUTION
Community Support

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.
5 REPLIES 5
Regular Visitor

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

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

Community Support

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.
Regular Visitor

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!

Community Support

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.

Post Prodigy

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

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

#### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors