Need help calculating PERCENTILES...

[Anonymous]

Hi Experts,

I have a dashboard where active user counts per line of business(LOB) are measured by user activity.

Now the new requirement is to create profiles based on the user activity percentile.

power - if activity is greater than 75 percentile

regular - if activity is greater than 25 percentile and less than 75 percentile

casual - if activity is greater than 0 percentile and less than 25 percentile

inactive - if there is no activity

These profiles has to be displayed in a pie chart.

And ones these profiles are created, i should be able to use these in calculating  adoption percentages

logic: (power+regular)/all users

Also i should be able to apply filters on these percentiles - like YEAR, MONTH.

Can someone please help me achieve this?

Any help is much appreciated. Thanks!

[emeehleder]

You can try to create variables for your different fractiles using an equation like this...

=fractile(((Power+Regular)/AllUsers),.75)  

=fractile(((Power+Regular)/AllUsers),.25)  

Then in your expression try something like if(((Power+Regular)/AllUsers)>=vFractile75,'Power')

[sunny_talwar]

Can someone please help me achieve this?

Sure, but where is the data? what have you already tried? from what you tried.... what all things were not working

[Anonymous]

Sorry Sunny for not providing all the details.

I've created a resident table to calculate the counts of user activities

And added this new table in the data model by USER ID

Now i'm trying to built the below chart as per the requirement.

The first part, count(USER) by USER_LOB is done.

I'm stuck at creating the profiles and using it in the expression to add next to the user count as adoption%.

Please let me know if any additional information is required. Thanks!

[shiveshsingh]

Can you share the excel?

[Anonymous]

Hi Shivesh,

Please see below:

I need to calculate the percentile for the whole data set and i should be able apply filters on that. Thanks!

[Anonymous]

Hi Erica,

I do not have the power , regular and other user profiles created right now.

can you please guide on how to create those profiles so i can try creating these variables using them? Thanks!

[Anonymous]

HI,

I created 2 Variables to calculate percentile

vPower = Fractile(user_profile,0.75)

vRegular = Fractile(user_profile,0.25)

and to calculate the adoption % I tried

if(USER_Profile >= vRegular, count(total(aggr(if(USER_Profile >= vRegular, count(user_profile)),user_profile),0)

/count({<IS_Legal = 1>} total user)

Issue I see here is when I don’t add USER_profile as a dimension,it is displaying 0 in the chart.

can someone please help me have the adoption percentage displayed without the USER_profile column added in the chart.

thanks!