cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hksl Helper I
Helper I

Total number of rows in the table visualization with 3 columns

I am new to Power BI. Using free download of  PBI Desktop. I have created table visual with 3 columns "Location_Nb", "SubLoc_Nb", "SubLoc Group Nb".  Is it possible to add row to this visualization : "Total number of rows" which would display total number of rows in this visualization?
Or count total number of rows in this visualization and display it in the Card visualization, for example?
I've read some posts, found some DAX syntax, but Total is a wrong number. "MyTable" is actual table used to create visuals.
Total should be 1250, but my DAX shows 89.
Here is my measure for Card visual:
Measure = COUNTROWS (filter(SUMMARIZE ('MyTable',[Location_Nb],"Row Count", 'MyTable'[Location_Nb]), [Row Count]>0))

Thank you

1 ACCEPTED SOLUTION

Accepted Solutions
Super User I
Super User I

Re: Total number of rows in the table visualization with 3 columns

Sure, it's:

 

	SUMMARIZE(
		FILTER(
			TableName,
			TableName[C1] = 96
		),
		TableName[C1],
		TableName[C2],
		TableName[C3]
	)

Did I answer your question? Please mark it as a solution.

Proud to be a Datanaut!

View solution in original post

16 REPLIES 16
BhaveshPatel Community Champion
Community Champion

Re: Total number of rows in the table visualization with 3 columns

Total Rows:=COUNTROWS(tablename)

 

You do not need to write complex formulas. The above formula will calculate the total no. of rows in your table. Just right click your table, select create measure and write the above DAX formula( Replace tablename with your tablename) and it will give you results explected which you can use in your card visual.

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
hksl Helper I
Helper I

Re: Total number of rows in the table visualization with 3 columns

Thank you BhaveshPatel but I need total nb of rows in Visualization of type "table", not in original table. Rows in "table visualisation" are result of me adding some fields from "dataset table" and applying filters. 

Super User I
Super User I

Re: Total number of rows in the table visualization with 3 columns

The COUNTROWS formula will count rows in the filtering context it's placed into. So if it's placed into a table visual that has some filters applied to the base table that it's counting, it will only count the resulting filtered rows.


Did I answer your question? Please mark it as a solution.

Proud to be a Datanaut!
hksl Helper I
Helper I

Re: Total number of rows in the table visualization with 3 columns

I have set Measure = COUNTROWS ('MyTable'). But now, in Card visualization,  it shows 4M (with Decimal Units = Auto) or 3884014 (with Decimal Units = none.) Whicg is total number of rows in Dataset table. 

There are only 1250 rows in the 'table visualization' with filters applied.  So, how to count those?

I am using Card visualization along with 'table visualization' becaus eI do not know how to add 'total nb of rows'  row to the 'table visualization'.

Thank you

Super User I
Super User I

Re: Total number of rows in the table visualization with 3 columns

If those filters are only on the table visual then they will only apply to the table. You would have to apply the same filters to the card, or to the entire page.


Did I answer your question? Please mark it as a solution.

Proud to be a Datanaut!
hksl Helper I
Helper I

Re: Total number of rows in the table visualization with 3 columns

Thanks again. I've applied 'page level' filters, it did not allow me to apply to card visual. I've changed filter to be sure of nb of records in 'tbl visual'. I can see that there are 26 rows in 'tbl visual', but Measure = COUNTROWS ('MyTable') shows me 269. 

Applying report level filters did not help.    

So, I have three columns: "C1", "C2", "C3".

'table visual' shows 26 rows for when C1 = 96 (filter). It seems some grouping of rows happens?

Measure = COUNTROWS ('MyTable') shows 269 - which is total nb of rows in original db table for C1 = 96

It feels like I need measure that reflects grouping applied to 'tbl visual'  ?

Super User I
Super User I

Re: Total number of rows in the table visualization with 3 columns

Are you just showing raw columns in your table visual or are you using measures there? If the only filter is C1 = 96 and you're just showing columns, it should show all the rows where C1 = 96 in the table visual. You say there are 269 rows in your table where C1 = 96 so why is your table visual not showing 269 rows?


Did I answer your question? Please mark it as a solution.

Proud to be a Datanaut!
hksl Helper I
Helper I

Re: Total number of rows in the table visualization with 3 columns

'tbl visual' - to created it I've drag/dropped C1, then C2, then C3.  I original table each C1 value has multiple C2 values associated with it. And each C2 value has multiple C3 values associated with it.  It feels that somekind of grouping happens when 

in the "Visualizations" pane :

"Values" shows C1, C2, C3.

"Filters" shows C1 is 96; C2 - no filtering value selected; C3 - no filtering value selected;

-----

I was able to reproduce in SQL how only 26 rows produced:

select MAX(C1), C2, C3
from MyTable
where C1= 96
group by C2, C3
order by C2, C3

 

It looks like all this logic was reated just by me adding columns to the 'tbl visual'

I did Not specify anywhere 'group by' conditions.

But this logic does not apply to 'card visual'

 

 

 

Super User I
Super User I

Re: Total number of rows in the table visualization with 3 columns

That makes a big difference. If there's grouping between the columns then you probably need to write a measure that replicates the table visual. You were on the right track by using SUMMARIZE. I think it will be something like:

 

Measure = COUNTROWS(
	SUMMARIZE(
		FILTER(
			TableName,
			TableName[C1] = 96
		),
		TableName[C1],
		TableName[C2],
		TableName[C3]
	)
)

 


Did I answer your question? Please mark it as a solution.

Proud to be a Datanaut!

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors