cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ShashankKalluri Frequent Visitor
Frequent Visitor

Grouping of two axis over a time period

Consider, I have a single table like below, where for each employee, I have the number of days worked in a month. 

 

Emp IDMonth YearDaysWorked
101Jan-1620
102Feb-1621
103Mar-1619
104Apr-1618
102Jan-1619
101Feb-1620
102Mar-1616
103May-1622
104Jan-1620

 

I have now the "Month Year" column as my filter. 

 

Functionality which I want to achieve is: If I select Jan'16 in the filter, I need to know the distribution of total number of days worked in the month vs count of workers (in a column chart)  i.e. 

Days WorkedCount of Workers
202
191

 

and for suppose, I select Jan'16 and Feb'16, it should aggregate the days worked in both the months and display the count of workers (in a column chart). for eg in the table above it would be: 

 

Days WorkedCount of Workers
402
201

 

 

and similarly for 3 months combination and so on.. If I select Feb'16 and Mar'16, it should calculate the sum of days worked in both the months and show it versus count of workers. 

 

Appreciate if anyone can help me out with this. 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Grouping of two axis over a time period

you want to group values by days, so you will actually need a table that has all possible sums of days
you can create it in DAX as as calculated table like this

DaysWorked = GENERATESERIES(1, 366, 1)

then you can add measure like that

NrOfEmployees = 
VAR __NrOfDays = MAX(DaysWorked[DaysWorked])
VAR __DaysPerEmployee = GROUPBY('Table','Table'[Emp ID],"NrOfDays",SUMX(CURRENTGROUP(),'Table'[DaysWorked]))
VAR __EmployeesPerDaySum = FILTER(__DaysPerEmployee,[NrOfDays]=__NrOfDays)
RETURN
COUNTROWS(__EmployeesPerDaySum)

in the visual you use DaysWorked[DaysWorked] and [NrOfEmployees]

1 REPLY 1
Highlighted
Super User
Super User

Re: Grouping of two axis over a time period

you want to group values by days, so you will actually need a table that has all possible sums of days
you can create it in DAX as as calculated table like this

DaysWorked = GENERATESERIES(1, 366, 1)

then you can add measure like that

NrOfEmployees = 
VAR __NrOfDays = MAX(DaysWorked[DaysWorked])
VAR __DaysPerEmployee = GROUPBY('Table','Table'[Emp ID],"NrOfDays",SUMX(CURRENTGROUP(),'Table'[DaysWorked]))
VAR __EmployeesPerDaySum = FILTER(__DaysPerEmployee,[NrOfDays]=__NrOfDays)
RETURN
COUNTROWS(__EmployeesPerDaySum)

in the visual you use DaysWorked[DaysWorked] and [NrOfEmployees]