Calculate Average Number of Items Assigned to a Person [UPDATED]

[Anonymous]

Hi all,

I need help to calculate how many ITEMS are Due or Overdue per USER by DIVISION or by ORGANISATION.

The relevant data are in 2 separate tables:

1. Learning Assignment
2. Users

The 2 tables can be correlated by the USER ID, that is unique for each user in the second table.

I am trying to build a measure that:

1. counts the items in the Learning Assignment Table;
2. counts all the ACTIVE USER in the Users Table  (column Active=TRUE)
3. divides the total number of items by the number of users
4. gives the number of items by user : Due or Overdue per USER by Division or  by Organisation

I have put a link to the data, all info is randomized

Link to Google Sheet with Data 

Samples of expected results:

DIVISION OPERATIONS, TOTAL ACTIVE USERS 137, TOTAL OVERDUE LEARNING 227

OVERDUE PER PERSON=1.65

Thank you!

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can create two measures to meet your requirement:

DueMeasure = 
DIVIDE (
    CALCULATE (
        COUNTROWS ( 'Learning Assignment' ),
        FILTER (
            'Learning Assignment',
            AND ( [Due Overdue] = "Due", [Division] = MAX ( 'Users'[Division] ) )
        )
    ),
    CALCULATE (
        COUNTROWS ( 'Users' ),
        FILTER ( 'Users', [Active (User)] & "" = "TRUE" )
    )
)

OverDueMeasure = 
DIVIDE (
    CALCULATE (
        COUNTROWS ( 'Learning Assignment' ),
        FILTER (
            'Learning Assignment',
            AND ( [Due Overdue] = "Overdue", [Division] = MAX ( 'Users'[Division] ) )
        )
    ),
    CALCULATE (
        COUNTROWS ( 'Users' ),
        FILTER ( 'Users', [Active (User)] & "" = "TRUE" )
    )
)

If it doesn't meet your requirement, could you please show the exact expected result based on the Tables that you have shared.

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can create two measures to meet your requirement:

DueMeasure = 
DIVIDE (
    CALCULATE (
        COUNTROWS ( 'Learning Assignment' ),
        FILTER (
            'Learning Assignment',
            AND ( [Due Overdue] = "Due", [Division] = MAX ( 'Users'[Division] ) )
        )
    ),
    CALCULATE (
        COUNTROWS ( 'Users' ),
        FILTER ( 'Users', [Active (User)] & "" = "TRUE" )
    )
)

OverDueMeasure = 
DIVIDE (
    CALCULATE (
        COUNTROWS ( 'Learning Assignment' ),
        FILTER (
            'Learning Assignment',
            AND ( [Due Overdue] = "Overdue", [Division] = MAX ( 'Users'[Division] ) )
        )
    ),
    CALCULATE (
        COUNTROWS ( 'Users' ),
        FILTER ( 'Users', [Active (User)] & "" = "TRUE" )
    )
)

If it doesn't meet your requirement, could you please show the exact expected result based on the Tables that you have shared.

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Thank you very much @v-lid-msft ,

I have opened the PowerPivot Data Model in Excel and input the formula (I also did my best to understand how it works, so I can actually improve my BI knowledge).

I am getting an error with the MAX function: this function does not work with STRING, it only works with Number or Dates.

I also was trying to understand, why are you using the MAX function here? What is the purpose?

I tried to change the formula like this, and it works, which is amazing, but I have literally no idea why...

Overdue Measure:=DIVIDE (
    CALCULATE (
        COUNTROWS ( 'LNR RC' ),
        FILTER (
            'LNR RC',
            [Due Overdue] = "Overdue")
        )
    ,
    CALCULATE (
        COUNTROWS ( 'Users Query' ),
        FILTER ( 'Users Query', [Active (User)] & "" = "TRUE" )
    )
)

I have downloaded the PBI Desktop  application (my bad, I did not know there was a standalone app!) and used your PBIX file, it works fine there, so I am a bit lost, is there a difference in the formulas between the data model in Excel and PBI desktop?

Yes, your figures are correct, the number that shows in the PBIX file calculation is the actual average number of DUE & OVERDUE training for the OPERATIONS division.

Also, I would like to have one single formula that is flexible enough to accept various filters, let me explain:

• Due or Overdue
• Division
• Manager
• Organisation
• Type of Training

Why isn't Excel smart enough to apply the filters in the pivot and I have to type them in in the code of the formula?

Can you please help me once more with the formula?

Thank you so much

L

[v-lid-msft]

Hi @Anonymous ,

Because the two measures are very similar, we will explain one of it

DueMeasure = 
DIVIDE (
    CALCULATE (
        COUNTROWS ( 'Learning Assignment' ),
        FILTER (
            'Learning Assignment',
            AND ( [Due Overdue] = "Due", [Division] = MAX ( 'Users'[Division] ) )
        )
    ),
    CALCULATE (
        COUNTROWS ( 'Users' ),
        FILTER ( 'Users', [Active (User)] & "" = "TRUE" )
    )
)

There are two part of it, Divide is a function to divide results of two part:

Part 1, Calculate function is calculte the first parameter in the condition of second parameter, Notice that this formula in the table visual, so every row will be different, such as Operation Rows, It will have a filter DIVIDE="Operation" in table, in our formula, is  'Users' , The first parameter is just count the rows of Learning Assignment using COUNTROWS function, the first parameter is to filter the table with two more condition, [Due OVerdue] field equal to "Due" and Division = max(users[DIVISION]), because the row context, the max(users[DIVISION]) is always equal to the current row of table visual.

CALCULATE (
        COUNTROWS ( 'Learning Assignment' ),
        FILTER (
            'Learning Assignment',
            AND ( [Due Overdue] = "Due", [Division] = MAX ( 'Users'[Division] ) )
        )
    )

So, finally the first part get the rows of learning assignment table with [Due Overdue] = "Due" and [Division] =the current division

CALCULATE (
        COUNTROWS ( 'Users' ),
        FILTER ( 'Users', [Active (User)] & "" = "TRUE" )
    )

The second part is same logic, but it count the users and because the row context in users table, we do not need to add another condition,use & "" is to convert the TRUE into STring, because maybe your type is true/false or string, we convert all of it as String to compare.

At last, we divide two part to get the final result you want.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-lid-msft 

Thank you again, you have been so helpful!

I have read your steps and the clarifications about the formulas, they are all clear now, except the MAX  I feel a bit stupid, I cannot understand why we need it.

In summary, so far:

1. I have been importing the data in Excel using a Query and than I used the PowerPivot window in Excel to do further manipulation on the data; there are a lot of big tables in my file; (I have to use Excel since, even if I start using PBI desktop, I would not know how to share the results in my company).
2. I have created the relevant connections between the tables
3. I have input your formula without the MAX, and it still works! I have also tried the formula removing the FILTER completely. it  still works, the results all correct, which is , once again stunnning. I just put a filter in the pivot table on the [Due or Overdue] field, and the calculations are all done.

I will do more tests, but it seems that the solution is fine for now.

I will try to see what happens if I add more filters.

[v-lid-msft]

Hi @Anonymous ,

Sure we will explain it in detail. First of all, you need to use Get Data to connect your excel file:

Choose the table you need and load them.

After get the data, we need to transform your table, go to the Power Query Editor,

User Table is great, but there are some problem in Learning Assignment Table, the head is the first row.

We just use the botton to make it normal:

Finally we close and save the change. 

We create a Table Visual and put the Division of Users Table into it

We will explanation the formula of measuers in next reply.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Just put it in a matrix table. Oranisation as rows, Users as Columns and COUNT of ID as the values. 

Then apply a filter on Due / Overdue with only the values "due" and "overdue"

[TomMartens]

Hey,

please provide your expected result to avoid any misunderstanding.

Regards,

Tom

[Anonymous]

Sorry! I did not give the correct / full info, I am chaning the main post.

[Anonymous]

OK, Main post changed and updated!