Forum Discussion
Trouble with filtering measure
I am trying to create a new measure that is filtered based on related results of another measure. To help explain what I am trying to do I have created a simple model.
In this simple model I have 4 tables (Date, User, UserAvlHours, and WorkedHours).
In the model I have some basic measures.
-Available Hours = SUM(AvlHours)
-Worked Hours = SUM(WorkedHours)
-User Utilization = DIVIDE([Worked Hours],[Available Hours])
On any given date a User may or may not have "Worked Hours" but will always have "Available Hours" and "Employee Utilization" will be calcuated for each user (which has worked hours).
In this simple example you can see the Total Utilization % is 66.25 which is 53/80, what I am trying to figure out is how can I create a filtered measure of Available Hours, that will ONLY show for Users that have Worked Hours. I would like to have the "Filtered Available Hours" only show for Users A - G and be blank for H, I, J. This would bring the total of "Filtered Available Hours" to 56 and the new "Filtered User Utilization" would be 53 / 56 =94.6%
What this filtering does is it gives me the utilization of Users that have done work (but ignores users that have no worked hours).
Thank you.
With these measures:
Available Hours by active users = SUMX( ADDCOLUMNS( SUMMARIZE( FILTER(UserAvlHours, NOT(ISBLANK([Worked Hours]))), User[UserID], 'Date'[Date]), "ActiveHours", [Available Hours]), [ActiveHours])User Utilization by active users = DIVIDE([Worked Hours], [Available Hours by active users])you will get...
6 Replies
PaulDBrownCommunity Champion
With these measures:
Available Hours by active users = SUMX( ADDCOLUMNS( SUMMARIZE( FILTER(UserAvlHours, NOT(ISBLANK([Worked Hours]))), User[UserID], 'Date'[Date]), "ActiveHours", [Available Hours]), [ActiveHours])User Utilization by active users = DIVIDE([Worked Hours], [Available Hours by active users])you will get...
This is exactly what I need. Thank you!
