Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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

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!

 

2 ACCEPTED SOLUTIONS
v-lid-msft
Community Support
Community Support

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" )
    )
)

 

 

4.PNG

 


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.

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.

View solution in original post

Anonymous
Not applicable

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

View solution in original post

9 REPLIES 9
v-lid-msft
Community Support
Community Support

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" )
    )
)

 

 

4.PNG

 


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.

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
Not applicable

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

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.

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
Not applicable

@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 Smiley Sad 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.

Hi @Anonymous ,

 

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

 

10.PNG

 

Choose the table you need and load them.

11.PNG

 

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

 

12.PNG

 

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

12.PNG13.PNG

 

We just use the botton to make it normal:

 

14.PNG15.PNG

 

Finally we close and save the change. 

 

16.PNG

 

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

 

17.PNG

 

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.

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
Not applicable

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
Super User
Super User

Hey,

 

please provide your expected result to avoid any misunderstanding.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

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

Anonymous
Not applicable

OK, Main post changed and updated!

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.