Forum Discussion
How can I create groupings within a PBI table?
I have a table in PBI that is pulling data from an MSSS database. I am receiving 4 columns of data: Order ID (unique to each line), Customer Name (each customer is in many rows), Order Description, and Cost. I have no problem creating a table with a column for each of these and one row per record. However what I am trying to do is, I want to create the table in a way that groups rows by Customer ID. For each customer ID, there will be a header row with the Customer Name and sum of Costs of all applicable rows. Under this header line, it would list all Order IDs and Order Descriptions for that customer. I would also like to alphabetize the order in which Customers are listed. I've done this a thousand times in Reporting Services, but I'm struggling to figure out how to accomplish it in PBI. If anyone knows how, the help would be greatly appreciated.
Hi, ThnkWthPrtls
I am glad to help you.
According to your description, you want realize the result shown in your picture?
If I understand you correctly, then you can refer to my solution.
Since you didn't give a specific dataset, I created a simple dataset based on what you meant:
I think you can do what you want with the matrix.
After connecting the data source, you can New Column to create the header row as you said:
Customer Total Cost = "CUSTOMER " & Table1[Customer] & "-" & " Total Cost " & CALCULATE ( SUM ( Table1[Cost] ), ALLEXCEPT ( Table1, Table1[Customer] ) )Then select the Customer field for sorting as shown in the image:
You can then select Matrix VISUAL and remove the subtotals:
Finally drag the corresponding field to the specified position as shown below:
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
ETA: I mocked up this sample of how I'd like the data laid out in Excel, in case this helps calrify what I mean
Hi, ThnkWthPrtls
I am glad to help you.
According to your description, you want realize the result shown in your picture?
If I understand you correctly, then you can refer to my solution.
Since you didn't give a specific dataset, I created a simple dataset based on what you meant:
I think you can do what you want with the matrix.
After connecting the data source, you can New Column to create the header row as you said:
Customer Total Cost = "CUSTOMER " & Table1[Customer] & "-" & " Total Cost " & CALCULATE ( SUM ( Table1[Cost] ), ALLEXCEPT ( Table1, Table1[Customer] ) )Then select the Customer field for sorting as shown in the image:
You can then select Matrix VISUAL and remove the subtotals:
Finally drag the corresponding field to the specified position as shown below:
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.This looks like it covers what I need, thanks a ton!
