Forum Discussion

JB_AT's avatar
JB_AT
Helper III
2 years ago

Dynamic Buckets

Hello

I have a measure that calulates the Employee Running Total
I also have a measure for Years of Service Running Total. Both work as expected.

What I would like to achieve is to create a dynamic bucket. The Bucket should contain the Amount of Employees that fall into a specific YoS bucket. 

Due to the complexity of the dataset behind above measures, I need to use them in any possible measure. 

I created a table with the Years of Services and have created this measure loosly based off other posts. But it doesn't return the result I want.

 

The end result should basically filter to the correct Years of Service when I choose different Years/Months, hense the use of Running Totals

Can anyone help?

 

 

 

 

VAR _MaxDate = MAX(DIM_Date[Date])
VAR Summary =
    SUMMARIZE (
        'Employee History',
       "Employee RT", [Employee RT],
        "Bucket", SWITCH (
            TRUE (),
            [Avg.Years of Service] >= 0 && [Avg.Years of Service] < 1, "0-1",
            [Avg.Years of Service] >= 1 && [Avg.Years of Service] < 3, "1-3",
            [Avg.Years of Service] >= 3 && [Avg.Years of Service] < 5, "3-5",
            [Avg.Years of Service] >= 5 && [Avg.Years of Service] < 10, "5-10",
            [Avg.Years of Service] >= 10 && [Avg.Years of Service] < 15, "10-15",
            [Avg.Years of Service] >= 15 && [Avg.Years of Service] < 20, "15-20",
            [Avg.Years of Service] >= 20 && [Avg.Years of Service] < 25, "20-25",
            [Avg.Years of Service] >= 25 , "25+"
        )
    )
RETURN
    SUMX ( Summary, IF ( [Bucket] = SELECTEDVALUE ( 'DIM Years Of Service'[Years Of Service Group]), 1, 0 ) )

 

 

 

Need Help

3 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Hi,

    Your expected result is not clear.  Share data in a format that can be pasted in an MS Excel file and show the expected result.

    • JB_AT's avatar
      JB_AT
      Helper III
      2 years ago

      Hi Ashish_Mathur 

      A Sample Data is here https://docs.google.com/spreadsheets/d/1h9pr1rixp-QJk6KplPci9BcZVS6Uigju/edit?usp=sharing&ouid=105131205756781664771&rtpof=true&sd=true 

      I want to create Dynamic Years of Service Buckets and Count how many UniqueIds fall into those buckets. My Date Table has a relationship with the __Valid_From and __Valid_Until columns. When I change the date slicer to lets say 2022, then the Years of Service Groups change to implement this date. I created these two measures that work correctly and shows me the correct data when I use the date filter. 

      I want to somehow use these measures to populate the buckets, or is there another way?

      Thanking you in advance

       

      Avg.Years of Service = VAR
    _MaxDate= MAX ( DIM_Date[Date] )
RETURN
CALCULATE (AVERAGEX(
    
         'Employee History',
         DATEDIFF ( 'Employee History'[__VALID_FROM], _MaxDate,DAY )/365.25
    ),
    FILTER ( ALL ( DIM_Date ), DIM_Date[Date] <= _MaxDate )
)

       

       

       

      Employee RT = 

VAR MaxDate =
    MAX ( DIM_Date[Date] )
RETURN
    CALCULATE (
        [Employees],
        KEEPFILTERS (
            'Employee History'[__VALID_FROM] <= MaxDate
                && 'Employee History'[__VALID_UNTIL] >= MaxDate
        )),
        ALL ( DIM_Date ), USERELATIONSHIP('Employee History'[__VALID_FROM], DIM_Date[Date]))