Calculated column: Divide fixed number by number of rows with same ID

[Anonymous]

Hi all,

Imagine a table like this:

I want a calculated column to divide a fixed number (In this example 20) with the Count of Job ID's per Project ID.

In above example :

Project ID 1 has 4 distinct Job IDs. Therefore each Job ID is 5 (20 / 4)

Project ID 2 has 8 distinct Job IDs. Therefore each Job ID is 2.5 (20 / 😎

How do I achieve that?

[Tahreem24 · Accepted solution]

@Anonymous Try this measure:

MEASURE = 
VAR perJOB_ = CALCULATE(COUNT(Table[Job ID],ALLEXCEPT(TableName,ProjectID))
RETURN DIVIDE(20,perJOB_)

[Tahreem24 · Accepted solution]

@Anonymous , Check the screen shot of your scenario:

[Tahreem24 · Accepted solution]

@Anonymous Try this measure:

MEASURE = 
VAR perJOB_ = CALCULATE(COUNT(Table[Job ID],ALLEXCEPT(TableName,ProjectID))
RETURN DIVIDE(20,perJOB_)