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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.