Total should come first column

[PBI_V2]

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

[mlsx4 · Accepted solution]

Hi @PBI_V2 

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

[PBI_V2]

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

[eliasayyy]

hello no it shouldnt create any issues with performance because you are just creating a new table so it is only calculated once 

[eliasayyy · Accepted solution]

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 front 

NewTable = 
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

[mlsx4 · Accepted solution]

Hi @PBI_V2 

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