Count Across Table of Number of Products with Total Product Amount

[Anonymous]

I am trying to figure out how to create a visual like this:

Product	Total Product Count	Total Amount
Cadency	3	315,413.04
RNET	4	215,749.07
Trecs	3	57,600.00
UPCS	2	10,000.00
Smart Analysis	1	5000
TOTAL	13	603,762.11

From my table like this:

Row Labels	Product 1	Product 1 Amount	Product 2	Product 2 Amount	Product 3	Product 3 Amount	Product 4	Product 4 Amount
Vao - Exhibit 11 - Mixed -  Implement RNET and Cadency	Cadency	187,855.03	RNET	104,308.12	Trecs	23800	UPCS	5000
LPL Mart- T&M - Exhibit C - Implement Cadency + ReconNET	RNET	52,875.28	Cadency	30,213.18		0		0
Wilbur - T&M - SOW 6725 - Upgrade RNET and ANET	RNET	45,400.00	Trecs	23,800.00		0		0
KSG- Schedule A-1 - Mixed - Migrate Certification to Cloud & Implement ReconNET	Cadency	97,344.83	RNET	13,165.67		0		0
Proque- Fixed Bid - SOW 6703 - Migrate RNET and Certification to Cloud	Trecs	10,000.00	UPCS	5,000.00	Smart Analysis	5000		0

[Ashish_Mathur]

Hi,

Here's a way to do it without creating 3 copies of the Table.

Hope this helps.

[v-piga-msft · Accepted solution]

Hi @Anonymous,

To get your desired output, you could follow the steps below.

1. Go to Query Editor and duplicate the table for 3 times, then you will get 4 tables.

2. Keep column  Row Labels, Product 1,Product 1 Amount and remove other columns in table 1, then rename the Product 1 to Product and Product 1 Amount to Product  Amount and you will get the table like below.

3. Do the same options for table 2, table 3 , table 4.

4. Click Append Queries as New.

5. Apply and Close and then you could create the table like below.

 

 More details, please refer to the attachment.

Best Regards,

Cherry

[Anonymous]

Thank you for the step by step on how to achieve this! Worked just like you showed.