Create a card with % of TOP N users

[sKaiNet]

I have two tables with relations. 

Users: UserId, UserName

Answers: UserId, AnswerId

Users:

Id Name

1  User1

2  User2

3  User 3

Answers:

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? 

[Greg_Deckler]

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

Measure 6 = 
VAR __topN = 1 //change this to how many you want
VAR __table = ADDCOLUMNS(ALL(Users),"__count",COUNTX(RELATEDTABLE(Answers),[Id]))
VAR __table1 = ADDCOLUMNS(__table,"__rank",RANKX(__table,[__count]))
RETURN
CONCATENATEX(SELECTCOLUMNS(FILTER(__table1,[__rank]<=__topN),"__name",[Name]),[__name],",")

[AlB · Accepted solution]

Hi @sKaiNet

Try this:

PercentageTop3 =
DIVIDE (
    CALCULATE (
        COUNT ( Answers[AnswerId] );
        TOPN (
            3;
            VALUES ( Answers[UserId] );
            CALCULATE ( COUNT ( Answers[AnswerId] ) ); DESC
        )
    );
    COUNT ( Answers[AnswerId] )
)

[sKaiNet]

---

@AlB wrote:

Try this:

PercentageTop3 =
DIVIDE (
    CALCULATE (
        COUNT ( Answers[AnswerId] );
        TOPN (
            3;
            VALUES ( Answers[UserId] );
            CALCULATE ( COUNT ( Answers[AnswerId] ) ); DESC
        )
    );
    COUNT ( Answers[AnswerId] )
)

---

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.

[AlB]

@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.

[Anonymous]

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 %.