Creating a visualization that groups and summarizes tabular data based on the value in a column

[PaulKraemer]

Hi,

I am new to Power BI Desktop.  Having read some of the online documentation, I managed to link to a data source (an ODBC datasource that is linked to a PostgreSQL database) and I selected a few tables in this database that I am interested building visualizations for.  Having done this, I went to Home | Queries | Transform Data | Transform Data on the Power BI ribbon to open the Power Query Builder.  In the Power Query Builder, I did some shaping of the data in one table that included filtering for a date range, removing a few columns, merging queries to pull in a field that is stored in a related table, and reordering columns.  After I had the data in this table shaped how I wanted, I saved the query in Power Query Builder and went back to Power BI Desktop.

In Power BI Desktop, I dragged several fields from my "shaped" table onto the canvas.  This automatically created a visualization in the format of a tabular display similar to the following:

Ticket No	Ticket Type	Ticket Date	Ticket Amount
1	Type A	11/01/24	$100
2	Type B	11/02/24	$200
3	Type C	11/03/24	$300
4	Type A	11/01/24	$150
5	Type B	11/02/24	$250
6	Type C	11/03/24	$350

... The table (and visualization) includes many rows of many tickets belonging to each ticket type (A, B, and C).  The bottom of the table shows the total of ALL Ticket Amounts.  What I'd like to do next is take this a step further by making the visualization group and summarize by Ticket Type.  (ie..I'd like to first see all the Tickets of Type A followed by the total of Type A ticket amounts, then all the Tickets of Typ B followed by the total of Type B ticket amounts, followed by the same for all the tickets of Type C before I see the total of ALL tickets at the bottom of the visualization.

I am sure this is possible, but I'm not sure where to begin.  When I look at all the Icons for the available visualization types (Stacked Bar Chart, Stacked Column Chart, etc), it is not even clear to me what the type of visualization that was automatically created for me is called.  If anyone here can give me some advice or point me towards a tutorial that covers what I hope to accomplish, I would greatly appreciate it.

Thanks in advance,
Paul

[rajendraongole1 · Accepted solution]

Hi @PaulKraemer -  I guess that you’re working with the Table visualization in Power BI Desktop. This visualization allows you to display data in a tabular format similar to a spreadsheet.

To achieve grouping and subtotaling, the Matrix visualization may be better suited than the Table visualization. The Matrix visualization lets you display subtotals for each group, making it ideal for grouping data by categories like Ticket Type.

you can drag the fields into rows, columns and values in Matrix chart.You should now see your data grouped by Ticket Type with subtotals and a grand total at the bottom.

In the Visualizations pane, go to the Format (paint roller icon) section.
Expand the Subtotals section to turn Row subtotals on or off as desired.
Customize the Grand Total section if needed to make the total more prominent.
Adjust the formatting for fields like Ticket Amount to ensure it appears as currency, if needed.

Ref link:

https://www.youtube.com/watch?v=qphVQdI5rQs&pp=ygUjUG93ZXIgQkkgbWF0cml4IHZpc3VhbCBpbnRyb2R1Y3Rpb24%3...

[v-tianyich-msft · Accepted solution]

Hi @PaulKraemer ,

According to your description, it seems that the sum is calculated for different Types, which can be achieved simply by a matrix, and you can check the result as follows:

Create a matrix visual in Power BI - Power BI | Microsoft Learn

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[PaulKraemer]

Hi Scott and Rajendra - 

Thank you very much for your replies.  You were both a very big help!

Best regards,
Paul

[rajendraongole1 · Accepted solution]

Hi @PaulKraemer -  I guess that you’re working with the Table visualization in Power BI Desktop. This visualization allows you to display data in a tabular format similar to a spreadsheet.

To achieve grouping and subtotaling, the Matrix visualization may be better suited than the Table visualization. The Matrix visualization lets you display subtotals for each group, making it ideal for grouping data by categories like Ticket Type.

you can drag the fields into rows, columns and values in Matrix chart.You should now see your data grouped by Ticket Type with subtotals and a grand total at the bottom.

In the Visualizations pane, go to the Format (paint roller icon) section.
Expand the Subtotals section to turn Row subtotals on or off as desired.
Customize the Grand Total section if needed to make the total more prominent.
Adjust the formatting for fields like Ticket Amount to ensure it appears as currency, if needed.

Ref link:

https://www.youtube.com/watch?v=qphVQdI5rQs&pp=ygUjUG93ZXIgQkkgbWF0cml4IHZpc3VhbCBpbnRyb2R1Y3Rpb24%3...