cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sKaiNet Frequent Visitor
Frequent Visitor

Create a card with % of TOP N users

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? 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Create a card with % of TOP N users

Hi @sKaiNet

Try this:

 

PercentageTop3 =
DIVIDE (
    CALCULATE (
        COUNT ( Answers[AnswerId] );
        TOPN (
            3;
            VALUES ( Answers[UserId] );
            CALCULATE ( COUNT ( Answers[AnswerId] ) ); DESC
        )
    );
    COUNT ( Answers[AnswerId] )
)
5 REPLIES 5
IvanDonev Frequent Visitor
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
Super User

Re: Create a card with % of TOP N users

Hi @sKaiNet

Try this:

 

PercentageTop3 =
DIVIDE (
    CALCULATE (
        COUNT ( Answers[AnswerId] );
        TOPN (
            3;
            VALUES ( Answers[UserId] );
            CALCULATE ( COUNT ( Answers[AnswerId] ) ); DESC
        )
    );
    COUNT ( Answers[AnswerId] )
)
Super User
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
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],",")

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


sKaiNet Frequent Visitor
Frequent Visitor

Re: Create a card with % of TOP N users


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

Super User
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.