Forum Discussion
Continued ContributorHow to count records in a table?
I have Free PBI Desktop v2.105 (64 bit free version May 2022) on 64-bit Windows Server 2019 Datacenter v1809.
I have a table with several columns in it, here are the column names and data type in parenthesis: Invoice date (date), Job Num (text), Customer name (text), Job Desc (text), and Invoice Amount (decimal). A slicer chooses the sales person and only shows data for that sales person. The Invoice Amount column gives me a total of all invoices at the bottom of the table which is what I want. But I'd also like to count the number of records shown at the bottom of the table, like how PBI shows the total Invoice Amount. Is that possible?
I have selected the table viz, and in the fields buckets I right click Job Num, and select Count but that changes the actual job in every row to a "1" which is not what I want.
An alternative is to use a Card viz to count the records. There are 23 records for sales person A but my card, set to count the Job num, shows 252,000 jobs, which is all of them in the Jobs table. Why doesn't the card only count the records in the table viz?
Thank you for your time!
Example output:
Invoice Date | Job num | Customer Name | Invoice Amt
1/21/22 | 1000 | Smith Industries | 1000.00
1/23/22 | 1001 | K&K | 2500.00
1/25/22 | 1002 | Smith Industries | 1000.00
2/1/222 | 1000 | Smith Industries | 50.00
Total | | 4 | 4550.00
8 Replies
- Greg_Deckler
Community Champion
croberts21 You could do this by using the technique found in Measure Total The Final Word. Essentially return the Job Number for the rows in your table but in the total line, return the number of records for the table visualization. This Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
croberts21Thank you. I'm not sure I understand though. The link you provided is about summing numbers. I want to count Job Num and put that count at bottom of the Job Num column.
- Greg_Deckler
croberts21 Right, instead of summing you would use COUNTROWS across your SUMMARIZE.
PC2790Hello,
I tried to visualise your data and created a table:
Invoice date Job Num Customer name Job Desc Invoice Amount
1-Jul-22 1 A Sales 230
15-Jul-22 2 B Marketing 400
12-Jul-22 3 A IT 100
15-Jul-22 1 B Sales 1000
30-Jul-22 4 A Marketing 200And then as per your requirement tried to create some visuals:
No selection onslicer
B selected on slicer:
Is that what you are after?
If yes, attached is the reference file for you.
If no, please further explain your requirement
- croberts21
Sort of. I would like, at the bottom of the table, the Count of Customer Name, without the Count Customer Name column.
I am unable to do HTML tables here so I will try to make a simple table here.
Invoice Date | Job num | Customer Name | Invoice Amt
1/21/22 | 1000 | Smith Industries | 1000.00
1/23/22 | 1001 | K&K | 2500.00
Total | | 2 | 3500.00
- PC2790
See if this helps:
_Measure = var _count = COUNTROWS ( InvoiceTable ) return IF ( HASONEVALUE ( InvoiceTable[Customer name] ), SELECTEDVALUE ( InvoiceTable[Customer name] ), _count )
