Forum Discussion
Post ProdigyGrouping in Power BI
Does Power BI posess the capability to group objects like SQL does? I have a table that I want to group but the table and Matrix visuals dont have any grouping function.
In the table pictured below , I have all duplicates until I get to the actual account codes and debit Credit Numbers. I want Only 1 Line for Imagetag, Docdate,Amount,Comments,Approver Invno, Currency and Status.
it should look like this
Hi lcasey,
>>Notice after changing to a matrix, there are many, many totals which is pretty useless and no one would ever be able to read a matrix that groups this way.
You can turn off the row total at format tab:
>>I did try a matrix , but the Power BI matrix is not as usable in Power BI as other reporting tools becouse you cant control the grouping.
If matrix not suitable for your requirement, you can try to use SUMMARIZE function to grouping the original table.
For example:
SUMMARIZE(Table,Table[Imagetag],Table[Amount],Table[Comments],Table[Approver],Table[Invno], "Total Currency ",SUM(Table[Currency]),"Total Status",SUM(Table[Status]))
Regards,
Xiaoxin Sheng
3 Replies
Hi lcasey,
You can use the matrix visual to achieve your requirement.
Drag the "Imagetag, Docdate,Amount,Comments,Approver Invno" columns to "Rows" filed, then drag the Currency and Status columns to "Values" field.
Regards,
Xiaoxin Shneg
lcaseyThanks for the suggestion. I did try a matrix , but the Power BI matrix is not as usable in Power BI as other reporting tools becouse you cant control the grouping. Notice after changing to a matrix, there are many, many totals which is pretty useless and no one would ever be able to read a matrix that groups this way.
Sample below:
Hi lcasey,
>>Notice after changing to a matrix, there are many, many totals which is pretty useless and no one would ever be able to read a matrix that groups this way.
You can turn off the row total at format tab:
>>I did try a matrix , but the Power BI matrix is not as usable in Power BI as other reporting tools becouse you cant control the grouping.
If matrix not suitable for your requirement, you can try to use SUMMARIZE function to grouping the original table.
For example:
SUMMARIZE(Table,Table[Imagetag],Table[Amount],Table[Comments],Table[Approver],Table[Invno], "Total Currency ",SUM(Table[Currency]),"Total Status",SUM(Table[Status]))
Regards,
Xiaoxin Sheng