cancel
Showing results for
Did you mean:
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 ID Month Year DaysWorked 101 Jan-16 20 102 Feb-16 21 103 Mar-16 19 104 Apr-16 18 102 Jan-16 19 101 Feb-16 20 102 Mar-16 16 103 May-16 22 104 Jan-16 20

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 Worked Count of Workers 20 2 19 1

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 Worked Count of Workers 40 2 20 1

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

## 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]

Highlighted
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]