Forum Discussion
Total should come first column
Hello All,
We are looking for a solution where in a matrix we can show total as First. like below
| Total | Jan | Feb | |
| USA | 100 | 60 | 40 |
Thanks,
PBI V2
Hi Anonymous
As far as I know there is no direct solution, but there are some tricks. Take a look at this link: https://www.youtube.com/watch?v=VRUs0pWgsdE
its not possible from the format however there is a workaround
lets say this is my table
then you need to create a new table that will add "Total" to each country as well as a sorting so total can be in frontNewTable = VAR OriginalWithSort = ADDCOLUMNS( SELECTCOLUMNS( 'Table', "Country", [Country], "Month", [Month], "Amount", [Amount] ), "SortOrder", SWITCH([Month], "Jan", 2, "Feb", 3, "Mar", 4, "Apr", 5, "May", 6, "Jun", 7, "Jul", 8, "Aug", 9, "Sep", 10, "Oct", 11, "Nov", 12, "Dec", 13, 1) ) VAR TotalWithSort = SELECTCOLUMNS( SUMMARIZE( 'Table', [Country], "TotalAmount", SUM('Table'[Amount]) ), "Country", [Country], "Month", "Total", "Amount", [TotalAmount], "SortOrder", 1 ) RETURN UNION( OriginalWithSort, TotalWithSort )
now sort the "Month" Column by the "Sort" column
add the fields in a new table visual from the newly created table above and turn off from format the column subtotal
result
4 Replies
- mlsx4Memorable Member
Hi Anonymous
As far as I know there is no direct solution, but there are some tricks. Take a look at this link: https://www.youtube.com/watch?v=VRUs0pWgsdE
- eliasayyyMemorable Member
its not possible from the format however there is a workaround
lets say this is my table
then you need to create a new table that will add "Total" to each country as well as a sorting so total can be in frontNewTable = VAR OriginalWithSort = ADDCOLUMNS( SELECTCOLUMNS( 'Table', "Country", [Country], "Month", [Month], "Amount", [Amount] ), "SortOrder", SWITCH([Month], "Jan", 2, "Feb", 3, "Mar", 4, "Apr", 5, "May", 6, "Jun", 7, "Jul", 8, "Aug", 9, "Sep", 10, "Oct", 11, "Nov", 12, "Dec", 13, 1) ) VAR TotalWithSort = SELECTCOLUMNS( SUMMARIZE( 'Table', [Country], "TotalAmount", SUM('Table'[Amount]) ), "Country", [Country], "Month", "Total", "Amount", [TotalAmount], "SortOrder", 1 ) RETURN UNION( OriginalWithSort, TotalWithSort )
now sort the "Month" Column by the "Sort" column
add the fields in a new table visual from the newly created table above and turn off from format the column subtotal
result - AnonymousNot applicable
Hello eliasayyy ,
Thanks for response, Is there any performance issue with this approch if case we have large volume of dataset.
Hello mlsx4
Thanks for response,
Is there any performace issue, we have create many to many relationship between tables in case we have large volume of dataset.
Thanks,
PBI V2
- eliasayyyMemorable Member
hello no it shouldnt create any issues with performance because you are just creating a new table so it is only calculated once