Forum Discussion

JPS_SEQ's avatar
JPS_SEQ
Regular Visitor
2 years ago

Power BI basic doubts

Hi, 

 

My database is a excel file with a list of several inputs.  One of the columns from that list is called "Name". 

I 'd like to create a top5 of the name most times inserted in that list. 

 

I create a table and apply FILTER N with TOP 5, and the top5 names appears. Howerver, the number of time for each name does not apperar in the table. Can anyone help me please ? 

 

 

Other question:

The databse uses two excel tabs.

I 'd like to calculate a ratio between two variables from each tab respectively. It is that possible ?

Need Help

3 Replies

  • audreygerred's avatar
    audreygerred
    Super User
    2 years ago

    If I understand correctly, you want the Top5 Names based on how many times the name appears in the list - if that is correct, you can make a measure that will count the rows like, Name Count = COUNTROWS(YourTable) COUNTROWS function (DAX) - DAX | Microsoft Learn

    Then, when you do your TopN filter, put the measure you made in the 'By value' section, so it will return you the Top5 Names based on the countrows measure.

  • Trevor_G's avatar
    Trevor_G
    Frequent Visitor
    2 years ago

    Question 1: 

    There are multiple ways to handle this. 

    Here is one: 

    Create a new table with only the topN results: 

     

    Create a measure to count the times the name apprears in the first table: 

     

    Changes the 'Value' settings to be 'Count' (rather than sum):

     

    Final results (both tables side by side): 

     

    The name Oscar does not appear on the second table, and count of each name is on the second table

     

     

    Question 2: 

    This is possible. 

     

    For a basic design on how to achieve this, you should use a 'Dimensional' table to connect tables together (Model View shown below): 

     

    These types of tables connect the data together and allow for calculation to be performed: 

     

    Here is the measured used to do this: 

     

    Here are the columns used to make the final visual: 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Thanks solutions from Trevor_G and audreygerred , your solution is great!

    Hi, JPS_SEQ 

    Pls have you solved the current problem as well? If so, you can mark a helpful reply as a solution so that other members of the community can quickly find a solution if they encounter similar doubts or problems.

     

    Best Regards

    Jianpeng Li