Forum Discussion

DriesD's avatar
DriesD
Regular Visitor
6 years ago
Solved

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...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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.
sanimesa's avatar
sanimesa
Post Prodigy
6 years ago

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.