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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
benmcmahon
Helper II
Helper II

Customer segmenting measure not showing all values.

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:
 
As always any help is very much appreciated.
 
Screenshot 2022-10-13 163859.png

 

 

Screenshot 2022-10-14 102106.png

 

 

 

 

Screenshot 2022-10-14 102353.png

2 REPLIES 2
changqing
Resolver II
Resolver II

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.

 

changqing_2-1665915177726.png

PERCENTILEX.INC

 

regards

CQ

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.

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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