Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Bhuvaraghan
Frequent Visitor

Creating Dynamic Groups

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?

 

DateEmployee IDEmployeeNameReporting AuthorityShiftLocationProduction #TargetProductivity %
2-Mar-2118CHNightAustralia727596%
5-Mar-2119CHNightAustralia617581%
9-Mar-215AFDayUSA527569%
9-Mar-2121DINightAustralia567575%
11-Mar-214AFDayUSA9475125%
14-Mar-2115CHNightLondon647585%
27-Mar-2120DINightAustralia617581%
28-Mar-218BGDayLondon717595%
5-Apr-2124DINightAustralia507567%
8-Apr-211AFDayUSA8575113%
11-Apr-2110BGDayLondon517568%
16-May-2125DINightAustralia537571%
2-Jun-213AFDayUSA8075107%
5-Jun-2117CHNightLondon8375111%
17-Jun-219BGDayLondon8475112%
19-Jun-2123DINightAustralia637584%
24-Jun-217BGDayUSA587577%
28-Jul-2114CHNightLondon517568%
3-Aug-216AFDayUSA7975105%
18-Aug-2112BGDayLondon10075133%
31-Aug-2116CHNightLondon637584%
3-Oct-2111BGDayLondon647585%
14-Oct-2122DINightAustralia8675115%
24-Oct-2126DINightAustralia607580%
28-Oct-2113CHNightLondon607580%
29-Oct-212AFDayUSA8875117%
7 REPLIES 7
Bhuvaraghan
Frequent Visitor

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. Visual.PNG

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

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

AlexisOlson_0-1637958359027.png

 

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 ) )
        )
    )

 

AlexisOlson_1-1637958449569.png

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?

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 ) )
        )
    )

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
Super User
Super User

Can you specify what the end result should look like?

Hi Alexis Olson,

 

I have attached my requirement above.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.