cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DriesD
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 CodeOrdernumberCreated On
US110011/5/2020
US110021/13/2020
US110031/7/2020
CA110041/8/2020
CA110051/10/2020
CA110061/10/2020

 

Second table

Company CodeTechnicians
US110
CA15

 

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
v-juanli-msft
Community Support
Community Support

Hi @DriesD 

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

total = DISTINCTCOUNT(Table1[Ordernumber])

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

Capture3.JPGCapture4.JPG

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

Could you give me an example?

Capture5.JPG

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.

View solution in original post

5 REPLIES 5
DriesD
Regular Visitor

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
Community Support
Community Support

Hi @DriesD 

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

total = DISTINCTCOUNT(Table1[Ordernumber])

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

Capture3.JPGCapture4.JPG

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

Could you give me an example?

Capture5.JPG

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.

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!

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
Post Prodigy
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.
PBI Help orders per tech.png
 
 

Helpful resources

Announcements
Carousel_PBI_Wave1

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.

Power BI Summit Carousel 2

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!

BizApps LATAM 2023

Business Application LATAM Summit 2023

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

Power Platform Bootcamp

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.