cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors