cancel
Showing results for
Did you mean:
Frequent Visitor

## Create a card with % of TOP N users

I have two tables with relations.

Users:

Id Name

1  User1

2  User2

3  User 3

Id   UserId

1    1

2    2

3    1

4    1

5    2

etc.

I want to find top 3 users by a number of answers, sum the total number of answers of those top 3 users and display in a card % of all answers for those top 3 users have. I don't have issues to find the sum of all answers, but how to find the sum of answers by top 3 users?

Is that possible to that without additional tables? Using measures and \ or calculated columns only?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Create a card with % of TOP N users

Hi @sKaiNet

Try this:

```PercentageTop3 =
DIVIDE (
CALCULATE (
TOPN (
3;
)
);
)```
5 REPLIES 5
Frequent Visitor

## Re: Create a card with % of TOP N users

Here is a similar thread - https://community.powerbi.com/t5/Desktop/calculating-percentage-of-grand-total-of-a-total-per-custom... You will have to create two measures - one to get the sum of answers for your top N users and another one to calc the %.
Super User

## Re: Create a card with % of TOP N users

Hi @sKaiNet

Try this:

```PercentageTop3 =
DIVIDE (
CALCULATE (
TOPN (
3;
)
);
)```
Super User

## Re: Create a card with % of TOP N users

Probably better ways to do this, but something like this should work:

```Measure 6 =
VAR __topN = 1 //change this to how many you want
RETURN
CONCATENATEX(SELECTCOLUMNS(FILTER(__table1,[__rank]<=__topN),"__name",[Name]),[__name],",")```

Proud to be a Datanaut!

Highlighted
Frequent Visitor

## Re: Create a card with % of TOP N users

@AlB wrote:

Try this:

```PercentageTop3 =
DIVIDE (
CALCULATE (
TOPN (
3;
)
);
)```

Thank you, that works!

Could you please explain how TOPN works in your sample? From my POV TOPN gets top N rows by comparing values in a single column. However, you use CALCULATE as the third parameter for TOPN, which confuses me.

Super User

## Re: Create a card with % of TOP N users

@sKaiNet We have the list of UserIds as base table for the TOPN. The CALCULATE is used to trigger context transition and have the COUNT only consider the AnswerIds for the user in the current row. Without the CALCULATE, the expression would always yield the total COUNT() of AnswerIds for all UserIds. They all would thus come up in first place (since the OrderBy_Expression has the same value for all of them). TOPN actually is an iterator with row context. https://dax.guide/topn/ What is going on? The site functionality is down again? The editor doesn't show any option? I tried clearing the cache as suggested in a post but that won't do it.