Forum Discussion
buttercream
5 months agoHelper II
Need help creating a measure
Hello, My data looks like this: Employee Group Date Leave 112 Finance 1/7/2025 1 112 Finance 1/8/2025 1 112 Finance 1/11/2025 1 112 Finance 1/12/2025 1 112 F...
- 5 months ago
My apologies but I'm trying to calculate it by month. Example below.
Employee Group Date Leave 101 Finance 2/12/2025 1 101 Finance 2/13/2025 1 101 Finance 2/14/2025 1 101 Finance 2/15/2025 1 101 Finance 2/16/2025 1 101 Finance 3/20/2025 1 101 Finance 3/21/2025 1 101 Finance 3/22/2025 1 107 IT 1/22/2025 1 107 IT 1/23/2025 1 107 IT 1/24/2025 1 107 IT 1/25/2025 1 107 IT 2/3/2025 1 107 IT 2/4/2025 1 108 IT 2/1/2025 1 108 IT 2/2/2025 1 108 IT 2/3/2025 1 108 IT 2/4/2025 1 112 Finance 1/7/2025 1 112 Finance 1/8/2025 1 112 Finance 1/11/2025 1 112 Finance 1/12/2025 1 112 Finance 2/10/2025 1 113 IT 1/4/2025 1 113 IT 2/1/2025 1 114 IT 1/2/2025 1 114 IT 1/3/2025 1 114 IT 1/4/2025 1 120 Finance 1/2/2025 1 120 Finance 1/4/2025 1 120 Finance 2/1/2025 1 120 Finance 2/3/2025 1 132 Finance 1/2/2025 1 132 Finance 1/4/2025 1 132 Finance 1/5/2025 1 132 Finance 1/6/2025 1 132 Finance 2/3/2025 1 132 Finance 2/4/2025 1 132 Finance 2/5/2025 1 132 Finance 3/2/2025 1 132 Finance 3/3/2025 1 132 Finance 3/4/2025 1 I should see 2 employees for January and 1 for February for a total of 3 as highlighted in red above.
Here is the result from your measure:
- 5 months ago
Hi buttercream here ISINSCOPE handles the total row correctly by summing the monthly values instead of recalculating across all months. Sharing the PBIX file for reference.
- 5 months ago
buttercream
Assuming you have a period column the following DAX expression should give you what you're looking for:Finance Emp 3+ Leaves = VAR _Group = "FINANCE" VAR _Base = SUMMARIZE( CALCULATETABLE( 'Table', 'Table'[Group] = _Group ), [Employee], [Period] ) VAR _CheckLeave = FILTER( ADDCOLUMNS( _Base, "Leave", VAR _Emp = [Employee] VAR _Period = [Period] RETURN CALCULATE(SUM('Table'[Leave]), 'Table'[Employee] = _Emp, 'Table'[Period] = _Period) ), [Leave] > 3 ) RETURN COUNTROWS(_CheckLeave)I've attached a sample pbix for you.
buttercream
5 months agoHelper II
I'm not using a slicer. I want the measure to show # of finance users with >3 leaves. Something like the below
Employees with > 3 leaves = COUNTROWS(FILTER(VALUES(Data[Employee]),[L]>3 && Data[Group]="Finance")
but that doesn't work.
Ashish_Mathur
5 months agoSuper User
This works
Employees with > 3 leaves = CALCULATE(DISTINCTCOUNT(Data[Employee]),Data[Group]="Finance",FILTER(VALUES(Data[Employee]),[L]>3))
Hope this helps.