Creating Dynamic Groups

[Bhuvaraghan]

I have line level data of production and productivity %. I want to aggregate the productivity % at each level of selection. I am currently calculating manually at an employee MTD level using custom columns. I want to calculate this using DAX, any ideas?

Date	Employee ID	EmployeeName	Reporting Authority	Shift	Location	Production #	Target	Productivity %
2-Mar-21	18	C	H	Night	Australia	72	75	96%
5-Mar-21	19	C	H	Night	Australia	61	75	81%
9-Mar-21	5	A	F	Day	USA	52	75	69%
9-Mar-21	21	D	I	Night	Australia	56	75	75%
11-Mar-21	4	A	F	Day	USA	94	75	125%
14-Mar-21	15	C	H	Night	London	64	75	85%
27-Mar-21	20	D	I	Night	Australia	61	75	81%
28-Mar-21	8	B	G	Day	London	71	75	95%
5-Apr-21	24	D	I	Night	Australia	50	75	67%
8-Apr-21	1	A	F	Day	USA	85	75	113%
11-Apr-21	10	B	G	Day	London	51	75	68%
16-May-21	25	D	I	Night	Australia	53	75	71%
2-Jun-21	3	A	F	Day	USA	80	75	107%
5-Jun-21	17	C	H	Night	London	83	75	111%
17-Jun-21	9	B	G	Day	London	84	75	112%
19-Jun-21	23	D	I	Night	Australia	63	75	84%
24-Jun-21	7	B	G	Day	USA	58	75	77%
28-Jul-21	14	C	H	Night	London	51	75	68%
3-Aug-21	6	A	F	Day	USA	79	75	105%
18-Aug-21	12	B	G	Day	London	100	75	133%
31-Aug-21	16	C	H	Night	London	63	75	84%
3-Oct-21	11	B	G	Day	London	64	75	85%
14-Oct-21	22	D	I	Night	Australia	86	75	115%
24-Oct-21	26	D	I	Night	Australia	60	75	80%
28-Oct-21	13	C	H	Night	London	60	75	80%
29-Oct-21	2	A	F	Day	USA	88	75	117%

[Bhuvaraghan]

The visual I am currently using is a donut chart where I aggregate users based on productivity level. 0-90%, 91-95%,96%-100% and >100%. I am using MTD aggregation using custom columns and I'm able to acheive this. I'm trying to do the same calculation dynamically. If I choose my location as australia, I want the number of users to reflect only for australia, if I make any changes to time period I want it to calculate only for that period. 

 I want to apologize that I did not include in the original post.

[AlexisOlson]

Try creating a new Groups table to use for your groups.

Then you can define a measure to bucket the table rows into these groups:

GroupCount =
VAR Low  = SELECTEDVALUE ( Groups[Low] )
VAR High = SELECTEDVALUE ( Groups[High] )
RETURN
    COUNTROWS (
        FILTER (
            Prod,
            Prod[Productivity %] >= Low
                && ( Prod[Productivity %] < High || ISBLANK ( High ) )
        )
    )

[Bhuvaraghan]

Sorry for the delayed response. In my case count rows does not work as I want distinct count of Employee Name. Since Filter evaluates at a row level, the same employee is part of multiple bins. Any way to counter this?

[AlexisOlson]

Try this:

GroupCount =
VAR Low  = SELECTEDVALUE ( Groups[Low] )
VAR High = SELECTEDVALUE ( Groups[High] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Prod[Employee Name] ),
        FILTER (
            Prod,
            Prod[Productivity %] >= Low
                && ( Prod[Productivity %] < High || ISBLANK ( High ) )
        )
    )

[Bhuvaraghan]

I tried this one and it does not work. Just give me a couple of days, and I will create a pbix file with the problem and desired solution. My explanation using text is very poor. Thanks for the continued support.

[AlexisOlson]

Can you specify what the end result should look like?

[Bhuvaraghan]

Hi Alexis Olson,

I have attached my requirement above.