Filtering by Department as a whole

Question

PersonIDDepartmentOvertimeRegularOT % (OT / (OT +REG))&nbsp;1Produce51033%2Produce1204.7%3Dairy31023%4Dairy52020%5Produce101050%6Liquor202050%&nbsp;Here is an example of the dataset I'm working with. I have a chart that shows Department by Overtime Percentage. I want to show departments that have a total over time percentage of above 25%. When I put a filter to look for OT% &gt; 25% then it removes each individual that does not meet the requirement instead of filtering based on the department, which is what I want to do. So for Produce Department, their total OT Percent is calculated as 29% [ (33 + 4.7 + 50)/3 ]&nbsp; But with the filter I would lose the 4.7% row.&nbsp;&nbsp;I've looked through a few posts that were similar but nothing I've tried so far is working correctly&nbsp;&nbsp;

jos_woolley · Accepted Answer

Hi,Try adding a new measure:OT % (Department) =
CALCULATE (
    DIVIDE (
        SUM ( MyTable[Overtime] ),
        SUM ( MyTable[Overtime] ) + SUM ( MyTable[Regular] )
    ),
    ALLEXCEPT ( MyTable, MyTable[Department] )
)and using that in the Filters pane instead.Regards

Forum Discussion

Filtering by Department as a whole

1 Reply

Related content

Filtering a table on the Max date by department

Discharge department

Flow Map, departing

Calculating FTE per department with Department movements

Filter

PersonID	Department	Overtime	Regular	OT % (OT / (OT +REG))
1	Produce	5	10	33%
2	Produce	1	20	4.7%
3	Dairy	3	10	23%
4	Dairy	5	20	20%
5	Produce	10	10	50%
6	Liquor	20	20	50%