Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Table visual rows count

Hello ,
you got any solution for adding sr. no in table visual , I stuck with same probleam like i want to show total number of rows in the table visual but for some column i used aggregate function like sum ,

want to display total rows in the table visual like
Regards ,

Pooja

 

Any suggetion ,

General Comment
Interesting Links
Need Help
Tutorial Requests
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Anonymous

     

    Try with this MEASURE

     

    Bacially try putting all the row fields inside Summarize function

     

    MEASURE =
COUNTROWS (
    SUMMARIZE (
        TableName[G/L Account],
        TableName[Customer],
        TableName[Assignment],
        TableName[Document number],
        TableName[Type],
        TableName[Posting Date],
        TableName[Year/Month],
        TableName[Year],
        TableName[BusA],
        TableName[Dos Date],
        TableName[Reference],
        TableName[PK]
    )
)

16 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    You should just be able to create a measure like:

     

    MyCount = COUNTROWS('Table')

or maybe

MyCount = COUNTROWS(CALCULATETABLE('Table'))
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      if the visual involves multiple tables data....this wont work..so how do we get count of rows in visual that is sourced from multiple tables and multiple filters? should we create a another kpi visual which will show count of rows and apply same fileds and filters?

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      HelloGreg_Deckler

      By this measure how can count Total no of rows in the visual.

      Cant tack directly becouse of  tack sum of Amount balance column (group by), so some no of rows in excel sheet  get minimize the count then visual table.

      want to show table visual count of rows.
      Regards ,
      Pooja

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      HelloGreg_Deckler

      By this measure how can count Total no of rows in the visual.

      Cant tack directly becouse of  tack sum of Amount balance column (group by), so some no of rows in excel sheet  get minimize the count then visual table.

      want to show table visual count of rows.

      Regards ,
      Pooja

       

      • v-juanli-msft's avatar
        v-juanli-msft
        Community Support
        8 years ago

        Hi Anonymous

        Yes, it can display on a card chart.

        Measure = COUNTROWS(Sheet1)

        If you have 10 rows in the excel, when import to Power BI, your table shows 10 rows, then the function "COUNTROWS" will count all rows of your table despite you use any aggregation in the column.

        But if the number of rows in the excel is 8, then the table in Power BI shows 8 rows, you can't get more than 8 using "COUNTROWS" 

         

         

        Best Regards

        Maggie

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    8 years ago

    Hi Anonymous

    As Greg_Deckler said, the measure can calculate the total rows of the table depite some column you used aggregate function like sum.

     

    However, the total value can't show at the top of the table.

     

    Best Regards

    Maggie

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      v-juanli-msft

      Thanks for Reply,

      Want to show on Card visual.

      Pooja

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        8 years ago

        Anonymous

         

        Try with this MEASURE

         

        Bacially try putting all the row fields inside Summarize function

         

        MEASURE =
COUNTROWS (
    SUMMARIZE (
        TableName[G/L Account],
        TableName[Customer],
        TableName[Assignment],
        TableName[Document number],
        TableName[Type],
        TableName[Posting Date],
        TableName[Year/Month],
        TableName[Year],
        TableName[BusA],
        TableName[Dos Date],
        TableName[Reference],
        TableName[PK]
    )
)
  • Martaspg's avatar
    Martaspg
    Advocate I
    2 years ago

    Hello,

    I usually create a column called "nrows" with all values 1. Then you can put that variable in any visual and indicates the number of rows. It's simpler than to use CALCULATE when you have many columns.

    You can generate it in query (new column [nrwos]=1) or as a calcualted column [nrows]=1.

    I hope it helps

     

  • BonnieW's avatar
    BonnieW
    Regular Visitor
    9 months ago

    The basic problem here is displaying the correct number of rows in the visual outside of the context of the visual, i.e., in a card.

    If there are any filters applied from the filter pane on the visual level, they cannot be included outside of the context of the visual. You can use COUNTROWS(ALLSELECTED('tablename')) in a measure and then add that to a card, and it works great - as long as there are no visual-level filters applied from the filter pane.

    My solution has been to resign myself to seeing the number of rows in the Total row (which is always visible on the page if turned on, regardless of how many rows are displayed) by creating a measure like this, adding it to the table visual, and then changing the name of the measure in the visual to a blank. It isn't perfect, but until Microsoft updates the table visual to include an option to just display the total number of rows, it is the best I've found after many hours of research.

    RowsDisplayed_TotalOnly = IF( ISINSCOPE('DataTableName'[PrimaryKeyFieldName]), " ", COUNTROWS(ALLSELECTED('DataTableName')))

    For more details on usage and why it works, check my comment on Solved: How to display the count of the rows in a table vi... - Microsoft Fabric Community.