Customer segmenting measure not showing all values.

[benmcmahon]

Hello,

I am having an issue where I have a matrix tables, with names in the rows and months in the columns, and values being a measurable called 'samples'. On another page of my report, I have column charts which separarate these people into top 20%, 80-40% and bottom 40%. However, I am getting different values when adding up the total value for each month as compared to the Matrix table, mostly fopr 'Agents', not so much for clients.

Each chart and the respective matrix table use the same data from the same tables, those being 'AgentUKSampleCount', and 'ClientUKSampleCount'.

An example of the measure which segments my customers is:

Bottom 40 Agents =

var _s = SUMMARIZE(ALLSELECTED(AgentUKSampleCount),AgentUKSampleCount[AgentID],"sum",SUM(AgentUKSampleCount[NumSamples]))

var _1 = PERCENTILEX.INC(_s,[sum],0)

var _2 = PERCENTILEX.INC(_s,[sum],0.4)

var _0_40 = CALCULATETABLE(VALUES(AgentUKSampleCount[AgentID]),FILTER(_s,[sum]>=_1&&[sum]<=_2))

return

CALCULATE(COUNT(AgentUKSampleCount[AgentID]),FILTER(AgentUKSampleCount,[AgentID] in _0_40))

Here is the link to my pbix file:

https://techiongrouplimited-my.sharepoint.com/:u:/g/personal/ben_techiongroup_co_nz/ET6BNpl-cllMlGoC...

As always any help is very much appreciated.

[changqing]

Hi @benmcmahon ,

Not sure, this is my first exposure to the PERCENTILEX.inc function. When I looked at an example of it, I guessed that the reason it might be waiting is that it comes up with values that are not in the table parameters.

For example, the above percentilex. INC 0.75 returns 2.5, but this value is not in the raw table.

PERCENTILEX.INC

regards

CQ

[benmcmahon]

Hi @changqing,

I am not using PERCENTILEX.inc function to get a percentile of samples though. I am using the function to summarise customers based on samples, splitting them into percentile groups based on each customers ID.

Given this, none of the expected results would be in the original table I would think.

The returned result is not the result of the percentile function but should show the number of samples for each customer within the percentage group.