Ranking & Percentage Group

[adsa]

Hi,

I seem to have run into an issue when trying to segment my clients into groups with ranking.

I have a table Company that is linked to our policies table. A client can have multiple policies located in the policies table.

I have created by segment table (Top 25%, Mid 50%, Bottom 25%)

I would like to select a company (in a slicer) and find out how many distinct clients are there in the segment and at what purchase price.

I have gone through the tutorials but cannot break down the purchase price or clients by segment. It shows me all clients in the bottom segment.

Client Ranked = 
IF (
    ISBLANK ( [ScheduleNetCurrent] ),
    BLANK (),
    RANKX (
        FILTER (
            ALL ( AcquistionsSchedule[MasterEntity] ),
            NOT ( ISBLANK ( [ScheduleNetCurrent] ) )
        ),
        [ScheduleNetCurrent],
        ,
        DESC
    )
)

Segmented Schedule Amount = 
VAR rankdimension =
    VALUES ( AcquistionsSchedule[MasterEntity] )
VAR totalclients =
    CALCULATE (
        COUNTROWS ( AcquistionsSchedule ),
        FILTER ( ALL ( AcquistionsSchedule[MasterEntity] ), [ScheduleNetCurrent] > 0 )
    )
VAR customerrank = [Client Ranked]
RETURN
    CALCULATE (
        [ScheduleNetCurrent],
        FILTER (
            rankdimension,
            COUNTROWS (
                FILTER (
                    'Client Segmentation',
                    customerrank > totalclients * 'Client Segmentation'[Low]
                        && customerrank <= totalclients * 'Client Segmentation'[High]
                )
            ) > 0
        )
    )

Thank you in advance

[sam.mckay]

Just a bit too complex to really understand the issue by looking at the formula.

Would have to see an example in a demo file of this to have a play around with it.

To me it looks fine, so I’m really unsure without seeing more.

I would confidently guess is to do with context somewhere in the formula.

What I would do is look to break out parts of the formula to make sure you are getting the right results in the right context.

This is the best way to audit each part of complex formulas like this.

Thanks
Sam

[adsa]

Hi,

Thanks for the response.

I have broken it up and the values look correct, however still cannot figure out what is wrong when it is together (Attaching sample files)

Yes it does appear to be an issue with the context but can’t see how i need to apply.

I am attaching a sample file.Sample.pbix (54.7 KB) Sample data.xlsx (22.8 KB)

Thanks

[sam.mckay]

Ok thanks,

First you don’t need CALCULATE around this. It’s doing nothing in this instance.

This all has to do with context.

I had a play around and I thought that this particular part of the formula was probably the issue, and it was

I just brought it out by itself and the problem was clear.

Total Clients = CALCULATE( COUNTROWS( Schedule ), FILTER ( ALL( Schedule[Master Entity] ), [ScheduleNet] > 0 ))

You have 211 clients here but your ranking only goes to 107.

So that is why so many are landing in the bottom group all the time.

Have a play around with this and see where you get to.

Thanks
Sam

[adsa]

Thanks for this,

I will give it a go and post results or any further questions.

[adsa]

So I managed to resolve the issue you mentioned with the following:

VAR totalclients =
    CALCULATE (
        COUNTROWS ( DISTINCT(Schedule[Master Entity] )),
        FILTER ( ALL ( Schedule[Master Entity] ), [ScheduleNet] > 0 )
    )

I now get 106 clients which seems right. However, still having issues with the segment breakup.

[sam.mckay]

Can you try one other thing and place the measure inside here instead of the variable.

See if this improves things for you.

[adsa]

Okay i think i have it right:

ScheduleNet = 
    SUM (Schedule[Schedule (Net)])

Client Ranked = 
IF (
    ISBLANK ( [ScheduleNet] ),
    BLANK (),
    RANKX (
        FILTER (
            ALL ( Schedule[Master Entity] ),
            NOT ( ISBLANK ( [ScheduleNet] ) )
        ),
        [ScheduleNet],
        ,
        DESC
    )
)

Total Clients = 
   CALCULATE (
        COUNTROWS ( DISTINCT(Schedule[Master Entity] )),
        FILTER ( ALL ( Schedule[Master Entity] ), [ScheduleNet] > 0 ))

Schedule Amount Segmented = 
    CALCULATE (
        [ScheduleNet],
        FILTER (
            VALUES ( Schedule[Master Entity]),
            COUNTROWS (
                FILTER (
                    Segments,
                    [Client Ranked] > [Total Clients] * Segments[Low]
                        && [Client Ranked] <= [Total Clients] * Segments[High]
                )
            ) >0
        )
    )

[adsa]

Now that i have that working, can you point me in the direction of how i can find out which clients are in that group as currently it is showing all clients.?

[sam.mckay]

Why don’t you just add the function to the master entity table.

I’ve done it below and it is filtering for the clients which are in that particular group

This is because the formula is doing the filtering for you and BLANKing everything else out that isn’t in the selection.

Thanks
Sam

[adsa]

Thanks for the feedback and assistance Sam.

Everything is working as intended.