Trouble creating top 10% table

[eire23452]

Hi all, 

I am looking at user data (specifically, how much time they have spent learning by month), and I want to create some groupings. For instance, I have all users and their average consumption, I have a table where I have filtered out bottom outliers, and I want to create a table which includes the top 10% of users by watch time. 

To do this, I first created a top 10% threshold: 

Next, I created a new table: 

And finally 3 measures to get the user count in this table, the total consumption, and the average consumption: 

The thing is, my table isn't returning the expected values, as there are many more rows which I have calculated to be in the top 10%. Is there another way of achieving what I'm trying to do? 

Many thanks in advance! 

[Anonymous]

Hi  @eire23452 , 

Couuld you share sample data and expected output?

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Best Regards,

Wearsky

[rajendraongole1]

Hi @eire23452 -there might be a simpler approach that ensures you capture exactly the top 10% without running into the issue of including more rows than expected.You can calculate the total consumption of the top users with this measure

create new tables 

Power users =
FILTER(
active_users_all,
active_users_all[Minutes Video Consumed] >= [Power users threshold]
)

You can calculate the number of users in this top 10% group by using a measure

as it is you calculated;

Power Users Count =
COUNTROWS(Power users)

Power Users Total Consumption =
SUMX(Power users, Power users[Minutes Video Consumed])

The average watch time for this top 10% group can be calculated

Power Users Average Consumption =
AVERAGEX(Power users, Power users[Minutes Video Consumed])

If this approach does not fix the issue, it may help to review the dataset and ensure there are no unintended filters or relationships that might be affecting the calculations.

hope this works

[eire23452]

Thank you for your reply! For some reason, when I create the Power Users table with the FILTER function as suggested, it doesn't work. It just returns all of the rows for some reason (nothing is filtered).