Forum Discussion

Ritesh_Air's avatar
Ritesh_Air
Post Patron
5 years ago
Solved

Dynamic Grouping - based on distribution bucket

Hello experts,

 

I am trying to figure out, I have a use case where customers get loyalty points when they buy stuff.

We 3 levels of loyalty:

Not yet Qualified -- 0 - 2500 points
Base -- between 2500 - 20000 points
Executive -- > More than 20000 points

I am able to achieve how many customers are in different category by using dynamic grouping.

 

 

but now I want to find how many customers are close to get to the next level. Something like:

Within 0 - 10% to get to Base (which means points greater than 2250 but less than 2500)
Within 10 - 20% to get to Base (which means points greater than 2000 but less than 2250)
Within 20 - 30% to get to Base (which means points greater than 1750 but less than 2000)

 

Within 0 - 10% to get to Executive (which means points greater than 18,000 but less than 20000)
Within 10 - 20% to get to Executive (which means points greater than 16,000 but less than 18,000)
Within 20 - 30% to get to Executive (which means points greater than 14,000 but less than 16,000) so on and so forth.

 

I put the sample file here:

 

Just to give an example: In this example, 4 are executive, 33 are base and 761 are not qualified.

 

 

For e.g.: In base category, there is 1 customer who is within the threshold of 10% to achieve Executive status (3584605). I want a count that customer in that bucket.

 

https://drive.google.com/file/d/1KH3cm0_xN9OsO7tYs8wZk0VmXxqJdmN-/view?usp=sharing

 

 

Any help is appreciated.

 

Thanks,

RK

Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    5 years ago

    Hi Ritesh_Air , 

     

    According to you description，you could create a measure as follows:

    Within 0 - 10% =
VAR _VALUE =
    CALCULATE (
        DISTINCTCOUNT ( 'Enrolled Growers'[rpt_grp_key_unique] ),
        FILTER (
            VALUES ( 'Enrolled Growers'[rpt_grp_key_unique] ),
            COUNTROWS (
                FILTER (
                    'Medallion Groups',
                    ( [Total Catalyst Points] >= 'Medallion Groups'[Max] * 0.9 )
                        && ( [Total Catalyst Points] < 'Medallion Groups'[Max] )
                )
            ) > 0
        )
    )
RETURN
    IF ( _VALUE <> BLANK (), _VALUE, 0 )

     

    Within 10 - 20% =
VAR _VALUE =
    CALCULATE (
        DISTINCTCOUNT ( 'Enrolled Growers'[rpt_grp_key_unique] ),
        FILTER (
            VALUES ( 'Enrolled Growers'[rpt_grp_key_unique] ),
            COUNTROWS (
                FILTER (
                    'Medallion Groups',
                    ( [Total Catalyst Points] >= 'Medallion Groups'[Max] * 0.8 )
                        && ( [Total Catalyst Points] < 'Medallion Groups'[Max] * 0.9 )
                )
            ) > 0
        )
    )
RETURN
    IF ( _VALUE <> BLANK (), _VALUE, 0 )

    And so on…

     

    The final output is shown below:

     

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.  

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Ritesh_Air , you need to have measure points

     

    Then you need to have a bucket table with start and end range and bucket name 

     

    You have to create measures like this one

    sumx(filter(values(customer[customer ID]) , [points] >= Min(Bucket[Min Value]) && [points] <= max(Bucket[Max Value]) ), [points])

     

    This means points are grouped at the customer's level and then the filter is a bucket. Now you can use bucket in your visual

     

    Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    5 years ago

    Hi Ritesh_Air , 

     

    According to you description，you could create a measure as follows:

    Within 0 - 10% =
VAR _VALUE =
    CALCULATE (
        DISTINCTCOUNT ( 'Enrolled Growers'[rpt_grp_key_unique] ),
        FILTER (
            VALUES ( 'Enrolled Growers'[rpt_grp_key_unique] ),
            COUNTROWS (
                FILTER (
                    'Medallion Groups',
                    ( [Total Catalyst Points] >= 'Medallion Groups'[Max] * 0.9 )
                        && ( [Total Catalyst Points] < 'Medallion Groups'[Max] )
                )
            ) > 0
        )
    )
RETURN
    IF ( _VALUE <> BLANK (), _VALUE, 0 )

     

    Within 10 - 20% =
VAR _VALUE =
    CALCULATE (
        DISTINCTCOUNT ( 'Enrolled Growers'[rpt_grp_key_unique] ),
        FILTER (
            VALUES ( 'Enrolled Growers'[rpt_grp_key_unique] ),
            COUNTROWS (
                FILTER (
                    'Medallion Groups',
                    ( [Total Catalyst Points] >= 'Medallion Groups'[Max] * 0.8 )
                        && ( [Total Catalyst Points] < 'Medallion Groups'[Max] * 0.9 )
                )
            ) > 0
        )
    )
RETURN
    IF ( _VALUE <> BLANK (), _VALUE, 0 )

    And so on…

     

    The final output is shown below:

     

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.