How to create this Matrix or Table visual in Report Builder

[OceanExplorer]

Hello, suppose these are the records:

Based on the above recordes, I was trying to get the below aggregated table in Report Builder - basically it calculates the payment groupped by Customer ID and State:

I tried a few times of placing State, Customer ID and Payment in Row/Column groups and Values

 

, but can't get the desired result. 

[Khushidesai0109 · Accepted solution]

Hiii @OceanExplorer 

• Insert a Table

  • In Report Builder, add a new Table from the toolbox.

• Assign Fields to Rows & Values

  • Drag "State" into the Row Groups pane.
  • Drag "Customer ID" under "State" in the Row Groups pane (this ensures Customer ID is grouped under each state).
  • Drag "Payment" into the Values pane and set the aggregation to Sum.

• Ensure Aggregation is Correct

  • Right-click the [Payment] field in the table and choose Expression.
  • Modify the expression to:
    =Sum(Fields!Payment.Value)
  • This ensures that the Payment values are summed for each State + Customer ID.

• Run the Report

  • Click Preview to verify the output.

If this helps, I would appreciate your KUDOS!
Did I answer your question? Mark my post as a solution!

[OceanExplorer]

Thank you, I got it solved by adding something extra on top of your steps. Because I don't want to see the + Expanded/Collapse view, I need to leave "Expand/collapse groups" box unchecked. 

 

[huzzapac]

@OceanExplorer  this solution might help you.

[AthmakuriRekha]

Hi @OceanExplorer ,

1. Navigate to insert --> Table --> Table Wizard. Select the data set from the wizard. 
2. From the list of columns, drag and drop state and customer id in the Row Group section and payment to values section and right click on payment and change it to sum. resulting wizard will be as shown below.

3. Click on Next and Finish it. It will add the table to the report. Click on run and the expected results are shown as below.

Hope this answers your query.

[parry2k]

@OceanExplorer as a tablix visual, put state, customer id, and in the 3rd column add expression, and in the expression add sum of the payment column and that should do it.

[OceanExplorer]

Hi Parry2k, could you specify the sum expression for the 3rd column payment? I right-click on the value cell of the 3rd columns and entered =sum(Field!Payment.Value), but the result still didn't group the customer ID, I am still seeing multiple records/rows for the same customer ID. 

[jarturohb]

Hi OceanExplorer,

Have you tried dragging and dropping the columns to the groups? 
Are you working in PBI web right?